Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
how do I make a "like for like" KPI in QlikView?
regards
Bastian
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
what do you mean by "like for like" ? Please explain ...
Oleg
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
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
You could also do it directly in the expression. Something like this:
if(sum({<Year={"<$(=max(Year))"}>} total <Store> Amount)<>0,sum(Amount))
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
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]
;
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
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
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