Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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..
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..