1 Reply Latest reply on Oct 17, 2016 7:42 AM by Jeremy Blaney

Calculate differences in values between fixed/user specified dates

Hi all,

I'm new to parameters - hoping someone can point me in the right direction.

I need to create a viz that calculates the delta, percent diff., etc. between the most recent month and a user-specified previous month. Here's what my data looks like:

Report Date
Person 1Movement Type 11509/30/2016
Person 1Movement Type 2509/30/2016
Person 2Movement Type 11009/30/2016
Person 2Movement Type 2759/30/2016
Person 1Movement Type 11406/30/2016
Person 1Movement Type 2506/30/2016
Person 2Movement Type 1956/30/2016
Person 2Movement Type 2746/30/2016
Person 1Movement Type 11103/31/2016
Person 1Movement Type 2803/31/2016
Person 2Movement Type 11053/31/2016
Person 2Movement Type 2803/31/2016

In other words, the current month (baseline) will always default to 9/30 (or whatever the most recent month is). The compare month needs to be user specified (i.e., they can select 6/30, 3/31, etc.).

I know I need to use parameters, but I'm complete new to that game. I also won't have a problem create trend arrows and all that.

Just need help getting started! Any help would be much appreciated!

• 1. Re: Calculate differences in values between fixed/user specified dates

Here's where I'm currently at...

Most Recent Report

I have my most recent report identified using the following calculated field (Most Recent Report Date):

[Report Date] = { FIXED : MAX([Report Date]) }

All Other Reports

The most recent report is separated from all other reports using the following calculated field (Compare Report Date):

IF [Report Date] = { FIXED : MAX([Report Date]) } THEN NULL

ELSE [Report Date]

END

I can put these two dimensions on my columns and use a table count to show the difference. Yay!

But it's the next step that's giving me trouble... With both dimensions on the columns, I have three options (all three dates).

I tried adding Compare Report Date to the filters to get rid of either Mar 31 or June 30. It works, but I have to leave "Null" enabled the quick filter, which is less than ideal.

I'm fairly certain a parameter will work here, but not sure where it comes into play.