TabVizAnalytics

Insights with Viz and Analytics

Running Total and Index – A Twilight Love Story

Scenario :
Business User want to See last 12 Months Running Total Dynamically?

Calculations to be used:
Index, Size and Running Total

Date Set Used : Sample Superstore

Let’s Build the View for Running Total:

Connect to Superstore Data

Step 1: Drag Order Date to Columns

Step 2: Right Click on the Year(Order Date) and change the format from Year to Month-Year

Step 3: It Should Look Like this

 

 

 

Step 4: Again right click on the Month(Order Date) and change the data points from Continous to Discrete

Step 5: It Should Look Like this

Step 6: Drag Sales Measure to Rows section

Step 7: Change the SUM(Sales) to Running total

View:

Step 8: Here comes the Tricky part, User wants to view Latest 12 Months dynamically

error most people do:

Validation : IF datediff(‘month’,[Order Date],today()) <=11 and datediff(‘month’, [Order Date],today())>=0
then “True” else “False” end

Above calculation will give last 12 Months from Today.

We will use this Calculated field in filter and select “TRUE”

It Should Look Like this

But if you see the running total December-2016 value is “15,63,986”, once you applied the filter your December-2016 value is “96,999”. My requirement is running total of Last 12 Months which my December 2016 value should start from “15,63,986”, not from “96,999”.

Becuase when you apply the filter, last 12 months data will come to worksheet after filtering out. But we don’t want that scenario we need all the Months data and want to show last 12 months in the worksheet.

The correct way to Do:

–>  viz which date is sorted Descending ( Max date to Min date)
–> Now Write Index Calculation

–> Click on the Index measure Created and select “Convert to Discrete” and Drag next to months Pill

–> You will get numbers from 1 to 47 ( which index usually does like Row number or Serial Number in a partition )

–> remove the Index Field ( its just to check whether the numbers are working Properly or not)

–> Calculation to restrict last 12 months:

Validation correct : if size()-index < 12 the “True” else “False” end

— Drag the Created Field to Filter and select “True”

Now we got the Running total as we want and as well last 12 Months and it is dynamic

The calculation which we created will apply to data Coming in to view and we are restricting visibility by 12 Months only.

« »

© 2019 TabVizAnalytics. Theme by Anders Norén.