Calculated Field using Grand Totals...

tableaucommunity@tableau.com — Fri, 06 Sep 2013 14:39:07 GMT

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?

We are having issues with posting a workbook that has connections to an Essbase server.

tableaucommunity@tableau.com — Fri, 06 Sep 2013 12:31:39 GMT

We are having issues with posting a workbook that has connections to an Essbase server. The workbook on the desktop works perfectly fine on the desktop, but once we post to the server, it starts to throw error messages like: "Already Connected to Server" or "The call to the function EssMdxExectueQuery failed".

Any suggestions?

Pictures of errors attached:

Content in Tableau Community Forums

Calculated Field using Grand Totals...

We are having issues with posting a workbook that has connections to an Essbase server.