Year quarters with volumes (Sum the volumes)

Wed, 31 Aug 2016 11:55:39 GMT

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:

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?

Re: Year quarters with volumes (Sum the volumes)

Wed, 31 Aug 2016 14:28:34 GMT

This one worked for me:

To get the total correct AND the correct volume numbers, based on quarters:

Current Quarter:

Sum(if (inQuarter(reportingdate, QuarterStart(Today()), 0), Volume))

Previous Quarter:

Sum(if (inQuarter(reportingdate, QuarterStart(Today()), -1), Volume))

etc..

topic Re: Year quarters with volumes (Sum the volumes) in App Development

Year quarters with volumes (Sum the volumes)

Re: Year quarters with volumes (Sum the volumes)