Analyzing changes to data between two dates

[#1]

Dear Community,

the obstacle of the moment is a waterfall chart showing the development of the companies opportunities between two dates. The user would select the start and end date of the time frame he/she wants to review in the GUI and the chart would then return the opportunity volume by the start date (no problem here), new opportunities added (no problem either), probability increases/decreases, volume increases/decreases until we have the opportunity volume by the selected end date (no problem either).

The data I have available are daily snapshots from the opportunity data, my main challenge is now to compare the snapshots of the two selected dates and determine which opportunities have e.g. increased in probability within the selected time frame.

Challenge 1: I cannot handle the row comparison in the script because the users want to be flexible in their date selections in the GUI. Comparison between any dates should be possible.

Challenge 2: In my data I do not have a single row per opportunity per snapshot date, but one row per opportunity product per date  the sum of the product values per date building the total opportunity value.

Data sample:

The user selects 01.04.2016 as the start date and 03.04.2016 as the end date for the waterfall chart.

The expected return (in numbers) would be:

Probability Increase (for QUOTE-0001 from low to high) for 400

Volume increase (for QUOTE-0002 from 550 to 650) for +100

[swuehl]

Maybe like this:

Create your probablilites as dual values with a numeric representation so it's easier to compare.

Create two variables

vMaxDate

=Max([Snapshot Date])

vMinDate

=Min([Snapshot Date])

Select your date range.

Create a chart with dimension Quote Number and as expression(s) something like

=If(Only({<[Snapshot Date] = {'$(vMaxDate)'}>}Probability)>Only({<[Snapshot Date] = {'$(vMinDate)'}>} Probability) ), Sum({<Date = {'$(=Max([Snapshot Date]))'}>}  Amount)

= Sum({<Date = {'$(vMaxDate)'}>}  Amount) - Sum({<Date = {'$(vMinDate)'}>}  Amount)

[#3]

Thank you very much for the fast input. The remaining challenge is, that I cannot select the Quote number in the chart - it is supposed to become a waterfall chart without any selected dimensions, just expressions. I assume I can use AGGR in this context but due to the complexity of the formula, could you advise how to include it?

[swuehl · Accepted solution]

Maybe like

=Sum(

     Aggr(

          If(Only({<[Snapshot Date] = {'$(vMaxDate)'}>}Probability)>Only({<[Snapshot Date] = {'$(vMinDate)'}>} Probability) ,Sum({<Date = {'$(=Max([Snapshot Date]))'}>}  Amount))

     , [Quote Number])

)

[#5]

Many, many thanks - works like a charm!