3 Replies Latest reply on Nov 8, 2018 1:35 PM by swaroop.gantela

# MoM calc with LOD

Hi all,

I need to calculate some measures based on Previous and Current months and show the YTD months in a table dashboard.

Here is a simplified version:

Calcs:

"Roll On":  IF "Previous Mth Amount" = 0 THEN "Current Mth Amount" END

"Roll Off":  IF "Current Mth Amount" = 0 THEN "Previous Mth Amount" END

These calculations should be done at the Account level and then summarized in a aggregated view.

AcctSepOctOct Roll OnOct Roll Off
123010123101230
23492341023400
7899789009789
Total1902320357101239789

The final visualization should be a Summary like this:

JanFebMarAprMayJunJulAugSepOctTotal YTD
Amount\$\$\$\$\$\$\$\$1902320357\$
Roll On\$\$\$\$\$\$\$\$\$10123\$
Roll Off\$\$\$\$\$\$\$\$\$9789\$

Here is a sample of how the data source looks like:

ClientAcctMonthAmount
A1232018/09/300
A2342018/09/309234
B3452018/09/309345
B4562018/09/309456
B5672018/09/309567
C6782018/09/309678
C7892018/09/309789
A1232018/10/3110123
A2342018/10/3110234
B3452018/10/3110345
B4562018/10/3110456
B5672018/10/3110567
C6782018/10/3110678
C7892018/10/310

I was able to create the measures using Table Calculation. It works if I show the data with the Acct field. However if I remove Acct in order to create the Summary view the calcs are done at the aggregated level and therefore giving wrong results.

Eg: With Acct field

Note:

Roll On value for [Acct 123, Oct] is 10,123

Roll Off value for [Acct 789, Oct] is 9,789

Without Acct field

Note:

Roll On value for [Oct] became 0

Roll Off value for [Oct] became 0

(the aggregation is done before the calcs)

What I could think of was mixing LOD with Table Calculations to guarantee that the calc measures would be done at the Acct level but it seems this is not allowed in Tableau. Any tips?

(See sample twbx attached)

Thanks.

• ###### 1. Re: MoM calc with LOD

Paulo,

I'm not sure if I quite got there, but maybe this can give ideas.

I think you can move [Account] to the detail shelf and then aggregate using WINDOW_SUM([cfRollOn]).

Once you've set the Table Calculation settings, you will get the total, but duplicated many times, as many

as there are accounts. Using the Index calculation, you can filter it down to just one copy.

(For the Index, I first put it on the filter shelf, then I adjust it's settings, then I filter to just "1").

The table calculation settings are quite particular. I may not have got it quite right, but below is what I used.

MoM calc with LOD

1 of 1 people found this helpful
• ###### 2. Re: MoM calc with LOD

Thank you swaroop.gantela. This is exactly the solution to the lower level MoM calc that I was looking for.

I replicated the idea in the actual dashboard and the calculations work.

I really appreciate your time to respond and providing the solution twbx.

Cheers!

• ###### 3. Re: MoM calc with LOD

Paulo,