Calculating Percentage Change as an expression within a table

[#1]

Hi all,

I'm new to qlikview, just started using it yesterday, and I am hoping to calculate week over week percentage change within a table, with a couple of caveats

The data is set up so that so that I have daily values, and then a previous 7 day cumulative total next to it. I need to calculate the percentage change week over week, but only on Sundays. So if you look at the chart below, I need to be able to calculate (Y-X)/X. However, the chart isn't fixed to only the last two weeks, so the function would have to be able to call on any given week and sunday.

Date	Week	Daily Values	Past 7 Days
Monday	1
Tuesday	1
Wednesday	1
Thursday	1
Friday	1
Saturday	1
Sunday	1		X
Monday	2
Tuesday	2
Wednesday	2
Thursday	2
Friday	2
Saturday	2
Sunday	2		Y

The final chart also needs to be represented by Week. So far in getting just the raw values I have been using but I am stumped as how to pull in the previous weeks value for the percentage change calculation.

sum([Past 7 Day Value])*(if(WeekDay([Date])='Sun',1,0))) to call out only that Sunday value.

If anyone has any idea how to get to this value please let me know. The simpler the better, but any advice would be appreciated.

[swuehl]

Not really sure if I understand your setting, but if you have the exact same table as shown above filled with the data needed and you want to calculate your (y-x)/x you could try using chart inter record functions like top():

=(top(sum([Past 7 Day Value]),14)-top(sum([Past 7 Day Value]),7))/ top(sum([Past 7 Day Value]),14)

please look into the chart inter record functions.

You  could add a condition to show the result only in certain row (2. Sunday):

=if(rowno(TOTAL) =14, (top(sum([Past 7 Day Value]),14)-top(sum([Past 7 Day Value]),7))/ top(sum([Past 7 Day Value]),14) )

Hope this helps,

Stefan

[#3]

Hey Stefan,

sorry for not specifying, but the table actually continues on with multiple weeks, so the expression would have to call any sunday regardless of where it actually is in the table.

Thanks though.

[swuehl]

Not sure if I fully understood your setting, I assume that you have Dates and created Weekday and Week from that, then a daily Value. You do the 7 day accumulation in the chart and you want to do the percentage calculation also in the chart? If you don't need to be sensitive to selections, some things can probably be put into the script.

I created a simple sample that might show you a way of how you can achieve what I think you want, please see attached.

Basically it is a straight chart with dimensions Week and Weekday and as cumulative expression for the last 7 days, I use:

rangesum(above(total sum(Value),0,7))

And for the Sunday to Sunday percentage:

if(Weekday=6,aggr(nodistinct  (sum(total<Week> Value)/above(sum(total<Week> Value)) -1) ,Week))

Hope this helps,

Stefan