2 Replies Latest reply on Sep 6, 2013 7:39 AM by Meghan Gonzales

    Calculated Field using Grand Totals...

    Meghan Gonzales

      I currently am creating a stacked bar visualization with 5 different measure values, all being calculated fields between several data connections - from Excel sheets that have static information, to a relationship database, Salesforce.com and an Essbase live connection. The relationship database we receive data refreshes every morning. The data in the system can be tricky when it comes to sales totals by invoices. The relationship database reports data by individual line items versus having invoice totals and there is no work around for the data source as this information is checked daily by leadership and needs to be as live as possible with little human involvement.


      I need to be able to filter down in a calculated field by product line number as well as if the grand total of an invoice is less than a certain amount; I need it to complete another equation to figure out a run rate.

      I can’t send my Tableau workbook, but here is an example to hopefully help layout what I’m trying to do.


      In this example: Grand Total is 223,582 and if I was looking at trying to figure out the run rate based on invoices less than 25,000. Of the 223,582 grand total, 92,164 were under 25K based on the grand totals of each individual invoice. 67 days in the quarter have past and I have 24 left ((92,164/67 )* 24). – the run rate number would be: 33,013.97. I’m trying to get to that number. I attached a picture of the quick table I put together based on Invoice number. I cannot filter on invoice number since they are ever changing. See pic 1 attached.


      I can currently complete this on the individual line items and the equation would look like something:

      IF ([pl_prod_line] = 'A100' or

      [pl_prod_line] = 'A110' or

      [pl_prod_line] = 'A112' or

      [pl_prod_line] = 'A114' or

      [pl_prod_line] = 'A116' or

      [pl_prod_line] = 'A212' or

      [pl_prod_line] = 'A310') And

      [current_qtr]=[fis_qtr] AND [Ext Amt]<25000 THEN

      ([Ext Amt]/(IF (TODAY() < #9/30/2013#)THEN (DATEPART('dayofyear',today()-1 ))-181 ELSE (DATEPART('dayofyear',today()-1 )-272)END))

      *(IF (TODAY() < #9/30/2013#)THEN 272-DATEPART('dayofyear',today()-1 )ELSE 365-DATEPART('dayofyear',today()-1 )END)

      ELSE 0 End


      But that gets me a run rate total of 39,844 since it is only filtering out line items greater than 25K, not invoice totals. If I would apply this equation to each segment, it would skew it greatly. See pic 2 attached.


      Long story short, is there a way to grand total separate line items in a calculated field by invoice and still be able to add criteria like product line etc?

      Invoice by Product Line Grand Total Sample pic 1.png

      Product line run rate example pic 2.png

        • 1. Re: Calculated Field using Grand Totals...
          Tom Barnes



          My thought would be to do a conditional filter on ih_inv_nbr by SUM(Ext Amt) < 25,000.  That will filter out all invoices over 25,000.  Then all you need to do is build your calculation to figure out your run rate, which it looks like you have already done.

          ([Ext Amt]/(IF (TODAY() < #9/30/2013#)THEN (DATEPART('dayofyear',today()-1 ))-181 ELSE (DATEPART('dayofyear',today()-1 )-272)END))


          Am I thinking this is simpler than it really is?  Probably, but thought I would throw my idea out there anyway.




          • 2. Re: Calculated Field using Grand Totals...
            Meghan Gonzales

            Thanks Tom - are you talking about filtering the whole worksheet on that condition? Other calculations in the stacked bar use all of the data and I couldn't filter it. I attached a sample of the stacked bar below... When I try to add it to my calcluated field (numerous ways) I get that lovely can't mix aggregate and non aggregate error.. Maybe I'm not following you on your suggestion....



            Stacked Bar Example.png