topic like for like KPI in QlikView

like for like KPI

btrompetter — Tue, 05 May 2009 18:47:31 GMT

Hello,

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

regards

Bastian

like for like KPI

Oleg_Troyansky — Tue, 05 May 2009 19:17:11 GMT

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

Oleg

like for like KPI

btrompetter — Tue, 05 May 2009 20:25:33 GMT

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

rwunderlich — Tue, 05 May 2009 23:54:55 GMT

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

like for like KPI

johnw — Wed, 06 May 2009 00:18:45 GMT

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

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

like for like KPI

btrompetter — Tue, 08 Sep 2009 15:21:21 GMT

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

like for like KPI

johnw — Wed, 09 Sep 2009 02:08:16 GMT

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]
;

like for like KPI

btrompetter — Fri, 11 Sep 2009 20:15:09 GMT

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

like for like KPI

Fri, 11 Sep 2009 22:20:52 GMT

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

like for like KPI

Fri, 10 Jun 2011 10:44:13 GMT

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