2 Replies Latest reply: Mar 22, 2010 6:00 AM by Jordi RSS

    YTD in a Graphic Table Simple

    Jordi

      Hi
      I'm Jordi Santos and I'm a Beginner in QlikView program.

      I'm trying to calculate the accumulate value of YTD and the accumulate of 12 month to day.
      I can calculate this values with above expression, and that's right.
      But when I select one month, I get a wrong value for YTD and 12M, only appear the value of the current month not the accumulate.
      What expression I have to use?
      I attach an example.

      Thanks

        • YTD in a Graphic Table Simple
          Piet_Orye

           


          Hello,
          for me the easiest (fastest, safest) way is to adapt your load script to do the calculations once and for all:
          > renamed your import to "TempMesos" to use as a temporary table
          > then use a load of the temp data, with a formula to keep a running count YTD while in the same year; not the sort & the peek() to get the previous value
          > totals per year via a second grouping load
          > delete temporary table
          TempMesos:
          Load * Inline [
          TheYear,TheMonth,Value
          2008,1,1387394
          2008,2,1376844
          2008,3,1309404
          2008,4,1366188
          2008,5,1309275
          2008,6,1253805
          2008,7,1351683
          2008,8,829485
          2008,9,1214027
          2008,10,1398640
          2008,11,1311982
          2008,12,1365247
          2009,1,1288279
          2009,2,1259527
          2009,3,1356829
          2009,4,1210463
          2009,5,1259276
          2009,6,1228695
          2009,7,1315699
          2009,8,807569
          2009,9,1196796
          2009,10,1334510
          2009,11,1283482
          2009,12,1309258
          2010,1,1257022
          2010,2,1248634
          ];

          Mesos:
          Load
          TheYear,TheMonth,Value,
          If(TheYear = Previous(TheYear), Value + Peek('YTD',-1,'Data'), Value) As YTD
          Resident
          TempMesos
          Order by TheYear,TheMonth;
          MesosT:
          Load
          TheYear, sum(Value) AS YTotal
          Resident
          TempMesos
          Group by
          TheYear;
          Drop Table TempMesos;


          Greetings,
          Piet