Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
btrompetter
Contributor III
Contributor III

like for like KPI

Hello,

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

regards

Bastian

1 Solution

Accepted Solutions
rwunderlich

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

View solution in original post

9 Replies
Oleg_Troyansky

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

Oleg

btrompetter
Contributor III
Contributor III
Author

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

rwunderlich

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

johnw
Champion III
Champion III

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

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





btrompetter
Contributor III
Contributor III
Author

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

johnw
Champion III
Champion III

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
Contributor III
Contributor III
Author

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

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

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