3 Replies Latest reply on Feb 29, 2016 11:06 PM by thea carp

# Calculate difference in value based on variable date parameters

Hi all

I have a very simple file with category, subcategory, volume of sales by month (month1, month2, month3).

Is there a way to calculate the difference in sales between 2 dates that should be selected as a parameter? I want in the final output the users to be able to select 1 date (either month1 or month2 or month3) and then a 2nd date (either month1 or month2 or month3) the my calculated field would compute the difference?

The trick here is that the user has the option to select any 2 dates

 category date subcategory volume vegetables 2016-1 parsnip 532396 vegetables 2016-1 peppers 201879 vegetables 2016-1 poatatoes 128718 vegetables 2016-1 tomatoes 111497 fruits 2016-1 apples 91941 fruits 2016-1 oranges 91031 fruits 2016-1 grapes 70161 fruits 2016-1 figs 63867 fruits 2016-1 pears 54493 vegetables 2015-12 parsnip 4242534 vegetables 2015-12 peppers 32321 vegetables 2015-12 poatatoes 42422 vegetables 2015-12 tomatoes 11123132 fruits 2015-12 apples 324242 fruits 2015-12 oranges 21321 fruits 2015-12 grapes 1231234 fruits 2015-12 figs 2321 fruits 2015-12 pears 13232 vegetables 2015-11 parsnip 31213 vegetables 2015-11 peppers 2131321 vegetables 2015-11 poatatoes 23 vegetables 2015-11 tomatoes fruits 2015-11 apples 423 fruits 2015-11 oranges 42322 fruits 2015-11 grapes 12332 fruits 2015-11 figs 8576 fruits 2015-11 pears 545
• ###### 1. Re: Calculate difference in value based on variable date parameters

Hi thea carp,

We dug a bit the question with a colleague and this might help you:

If datetrunc('month',[date])=[Start Date] then -[volume]

Elseif

datetrunc('month',[date])=[End Date] then [volume]

else 0 End

Then Sum() those on screen and add datetrunc('month',[Start Date]). Same thing goes for End date.

• ###### 2. Re: Calculate difference in value based on variable date parameters

Hello Thea Carp,

there are more examples here - Current vs Previous Period to Date Comparison  as well thanks to the great contributions of Rody Zakovich.

Patrick

• ###### 3. Re: Calculate difference in value based on variable date parameters

Amazing! Thank you so much for your fast reply! I'll give it a try.