3 Replies Latest reply: Jan 24, 2012 6:28 PM by Stefan Wühl RSS

    Calculating Percentage Change as an expression within a table

      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
      WeekDaily ValuesPast 7 Days
      Monday1

      Tuesday

      1

      Wednesday1

      Thursday1

      Friday1

      Saturday1

      Sunday1
      X
      Monday2

      Tuesday2

      Wednesday2

      Thursday2

      Friday2

      Saturday2

      Sunday2
      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.

        • Calculating Percentage Change as an expression within a table
          Stefan Wühl

          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

            • Calculating Percentage Change as an expression within a table

              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.

                • Re: Calculating Percentage Change as an expression within a table
                  Stefan Wühl

                  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