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

    MoM calc with LOD

    Paulo Vasconcelos

      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.