Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

btrompetter
New Contributor III

like for like KPI

Hello,

how do I make a "like for like" KPI in QlikView?

regards

Bastian

Tags (1)
1 Solution

Accepted Solutions

like for like KPI

Take a look at Oleg's wiki on using flags here:
http://community.qlik.com/wikis/qlikview-wiki/using-flags-to-avoid-if-formulas.aspx

One approach would be to set a "sameStore" flag to 0 or 1 (0-new store). Then an expression like:

sum(Sales * sameStore)

would exclude the new stores.

-Rob

9 Replies
MVP
MVP

like for like KPI

what do you mean by "like for like" ? Please explain ...

Oleg

btrompetter
New Contributor III

like for like KPI

Hello Oleg,

we compare retail stores from the current year with the last year. And the "like for like" KPI says that you can only compare stores from the current year with the same stores from the last year. So every new store in the current year must be ignored.

Bastian

like for like KPI

Take a look at Oleg's wiki on using flags here:
http://community.qlik.com/wikis/qlikview-wiki/using-flags-to-avoid-if-formulas.aspx

One approach would be to set a "sameStore" flag to 0 or 1 (0-new store). Then an expression like:

sum(Sales * sameStore)

would exclude the new stores.

-Rob

MVP
MVP

like for like KPI

You could also do it directly in the expression. Something like this:

if(sum({<Year={"<$(=max(Year))"}>} total <Store> Amount)<>0,sum(Amount))





btrompetter
New Contributor III

like for like KPI

Hello,

I like the idea of flags.

My Like for Like definition says that the store must have turnover in the same period in the last year. That means if I have 7. September 2009 which is a monday I have to check the 8. September in 2008 if there are also turnovers.

My concept is to load the previous year and than the actual year and than set a flag during the load proccess in the actual year if there is turnover in the last year.

But how do I check if a store has tunrover in both years during the load script?

Thanks for help.

regards

Bastian

MVP
MVP

like for like KPI

I'm not sure at this point what you mean by "same period", but one way to set a 1/null flag would be with a join. Something like this, perhaps:

[MyTable]:
LOAD
store
,date
,turnover
FROM data source
;
LEFT JOIN ([MyTable])
LOAD
store
,addmonths(date,12) as date
,if(turnover,1) as "prev year turnover flag"
RESIDENT [MyTable]
;

btrompetter
New Contributor III

like for like KPI

Hallo John,

the period could be a day, a week or a month wich is compared to the last year.

Is it possible to make nested ifs or if then else combinations in the loadscript?

regards

Bastian

Not applicable

like for like KPI

Bastian,

I've had to compare week over week and week/day over week/day in the attached sample. You could also do the same for month. I used standard week conventions so I could compare the Monday of week 9 to the previous years Monday of week 9, etc.

Tom



Not applicable

like for like KPI

Hi Bastian,

Did you ever come up with a work able solution for 'Like for Like' reporting. ie exclude all values that do not appear in all periods.

I need to to something simular.

Many thanks,

Lawrence Chapman

Community Browser