Calculating Year over Year

[Shir63]

hi to all,

i would like to know what is the best way to create an automated KPI for YoY.

this is what i have for choosing 2 Qtrs for example:

count({< QuarterDateStart=$(vMaxDate) >}distinct [Lead Number])
/
count({<QuarterDateStart=$(vMaxDatePY) >}distinct [Lead Number])

but how do i handle choosing 3 Qtrs?? how can qlik know which qtrs are being selected?

thank you!

[rubenmarin]

Hi, instead of QuarterDateStart you can use the Year field on set analysis, it will compare the quarter(s) selected on this year vs the last year.

count({<Year=$(vCY)>}distinct [Lead Number])
/
count({<Year=$(vLY)>}distinct [Lead Number])

vCY is = Max(YearField) or Year(Max(DateField))
vLY is = Max(YearField)-1 or Year(Max(DateField))-1

[Shir63]

thank you for your reply and time.

this works only when applying Year filter,

but when choosing "Year-Quarter" for example, the result is null "-".

do i need to modify the formula?

thanks,

[rubenmarin · Accepted solution]

Any field that also filters by year should be explicitly excluded in set analysis of the last year:

count({<Year={$(vCY)}>}distinct [Lead Number])
/
count({<Year={$(vLY)},[Year-Quarter]>}distinct [Lead Number])

What is happening is that you select 2022-Q2, and the divisor doesn't have any data that has Year=2021 and Year-Quarter=2022-Q?

The other issue with this is to pick the date range to limit to 2nd quarter in last year.... and furthermore, what should be the last year if the user selects quarters from 2020-Q4 to 2022-Q1?

If there is a selector for year and another for quarter this is solved, if year-quarter slector is avaible more questions needs to be solved.

A workaround could be to use dates in set analysis.

vMaxDate = Max(Date);
vMinDate = Min(Date);
vMaxDate_LY = AddMonths($(vMaxDate),-12);
vMinDate_LY = AddMonths($(vMinDate),-12);

count({<Date={">=$(vMinDate)<=$(vMaxDate)"},Year,[Year-Quarter]>}distinct [Lead Number])
/
count({<Date={">=$(vMinDate_LY)<=$(vMaxDate_LY)"},Year,[Year-Quarter]>}distinct [Lead Number])

[Shir63]

thank you,

your solution worked for me with some adjustment.

I appreciate it!