Compare Current 12 months to Prior 12 months

millsaz69 — Tue, 26 Jun 2018 17:05:22 GMT

I am new to Qlik Sense and I want to create a comparison of customer sales for prior 12 months and current 12 months to see if they increased or decreased in purchases.

For example if customer A buys $1000 of widgets in prior 12 months (Jun 1 2016 - May 31 2017) and then buys $2000 more in current 12 months (Jun 1 2017 - May 31 2018) they had an increase of $1000. If customer A bought $1000 in prior 12 and $500 in current 12 there would be a -$500 decrease.

I can't include the current month (June 2018) in the calculations so last day end on May 31,2018.

Any help in building the formulas or set analysis would be greatly appreciated.

I have written this in SQL already, but I am trying to avoid using SQL views and use Qlik.

Thank you!

Re: Compare Current 12 months to Prior 12 months

ruanhaese — Wed, 27 Jun 2018 12:42:37 GMT

We have no clue what your dimensions or anything looks like but the are several ways to do this,

so heres the theory.

You can either add a Flag Column in the backend when loading the script that marks

this transactions as being part of last years values. The con with this is its static and so

updates only on reloads.

If you need it to be dynamic (as the user selects different years on the frontend) then you will have

to do it through set analysis. Something like:

SUM( {< DateKey={">=$(vMinDate)<$(vMaxDate)"} >} Amount )

* vMinDate and vMaxDate are variables calculated on the fly

Note the double quote (dont use the single quote on this)

topic Compare Current 12 months to Prior 12 months in App Development

Compare Current 12 months to Prior 12 months

Re: Compare Current 12 months to Prior 12 months