Dynamic user selected date comparison variance calculations

Jacinta · June 24

I am seeking support on ideas for the best way to allow a user to manually select one date span and then select a second date span for comparison, so that the visuals show the variance of $'s as well as % from the first time span selected and the second date span selected.

The user does not want pre built time span variance calculations they want to be able to explore any dates from this year and last year and see the variance. Whether its between yesterday and the day before, or from yesterday to same day last year.

Thanks for any thoughts and ideas.

ArborRose · June 25

You can track the calendar differences by using your primary date field and creating fields for year({datefield}, month({datefield}, monthname({datefield}) and the use aggregates in Magic ETL to calculate current year, previous year, previous 2 years, etc. How deep you decide you need is based on whether you need it down to week, day, hour, etc.

If you are comparing, then you want to do CYTD versus the PYTD.

So a current year aggregate would be something like:

SUM(CASE WHEN YEAR(`date`) = YEAR(CURDATE()) 

    THEN `amount` 

    ELSE 0 

    END)

A previous year to date aggregate would be:

SUM(CASE

    WHEN YEAR(`date`) = YEAR(CURDATE()) - 1

    AND `date` < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

    THEN `amount`

    ELSE 0

END)

So if you are in June, previous year only calculates up through June of the previous year. And so on. From there you can find the deltas / differences and percentage differences.

And you can get further using variables but it gets more complex.

ArborRose · June 25

If you want to use variables, you could create variables for comparing date range A with date range B. You will need a start and end for A range, and a start and end for B range. Then use a beast most to identify what belongs to range A and what belongs to range B. Filter out the other.

CASE 

  WHEN `Date` BETWEEN `Start Date Range A` AND `End Date Range A` THEN 'A'

  WHEN `Date` BETWEEN `Start Date Range B` AND `End Date Range B` THEN 'B'

  ELSE 'Other'

END

Find totals and variance and from that variance %. Account for dividing by zero in %.

SUM(CASE WHEN `Group` = 'A' THEN `Amount` ELSE 0 END)


SUM(CASE WHEN `Group` = 'B' THEN `Amount` ELSE 0 END)


SUM(CASE WHEN `Group` = 'B' THEN `Amount` ELSE 0 END)

- SUM(CASE WHEN `Group` = 'A' THEN `Amount` ELSE 0 END)



CASE 

  WHEN SUM(CASE WHEN `Group` = 'A' THEN `Amount` ELSE 0 END) = 0 THEN NULL

  ELSE

    (SUM(CASE WHEN `Group` = 'B' THEN `Amount` ELSE 0 END)

    - SUM(CASE WHEN `Group` = 'A' THEN `Amount` ELSE 0 END))

    / SUM(CASE WHEN `Group` = 'A' THEN `Amount` ELSE 0 END)

END

Jacinta · June 26

awesome thanks I will give this variable approach a try

Dynamic user selected date comparison variance calculations

Answers

Categories