1 Reply Latest reply: Sep 1, 2016 3:24 AM by Jermaine Ramsaransing RSS

    Year quarters with volumes (Sum the volumes)

    Jermaine Ramsaransing

      Hi experts,

       

      I'm a beginner with qlik scripting. I have data loaded that comes from our postgres db.

      I have the following fields.

      name = string

      volume = integer

      reportingmonth = epoch miliseconds (e.g. 1472629930000)

       

      This is how I convert the reportingmonth to a qlik date: Date(MakeDate(1970, 1, 1) + round("reportingmonth"/1000) / (60 * 60 * 24)) as "reportingdate"

      See the attachment for my test data.

       

      I want a table in my qlik sense app that looks like this:

      name, Current Quarter, Previous Quarter

                15                  , 17

      Test1, 10

      Test2,                       , 12

      Test3, 5

      Test4,                       , 5

       

      Let's say the current quarter is equal to Q3 2016, all the volumes with an epoch timestamp, that fall in Q3, should fall in the "Current Quarter" column. Previous Quarter is equal to Q2 2016 and all the volumes with an epoch timestamp, that fall in Q2 should fall in the column "Previous Quarter".

       

      I know you can use this function: inquarter ('25/01/2013', '01/01/2013', 0), but how to use it in conjuction with my volume column or is there even a better solution?


      --edit

      I tried this:

      Sum({$<inQuarter(reportingdate, now(), 0)>}Volume), but I get an error: Error in set modifier.

      Sum({$<inQuarter(reportingdate, '08/31/2016', 0)>}Volume), but I get an error: Error in set modifier.


      I tried an if, I got something, but not totally:

      inquarters.PNG


      I used this: if (inQuarter(reportingdate, '01/01/2016', 0), Sum(Volume)), but as you can see, I need to hardcode my base_date as a string, I don't want that, I want to use the now() function. But then I don't get any results... Also the total shows only for the first one, I want it for all, directly on load, is that possible?