Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Thank you inadvance for the help with the following, I have tried to find answers to my questions here and on the web but have not succeeded with everything.
I want to create charts/tables in QlikView (version 11) with the following and I need the formulas for those:
1- "Three-year average for the previous 12 months rolling": ie the average of the past three rolling 12 months.
Tex 201201 should be = (0(200901) +355(201001) +264(201101)) / 3, see the attached Excel file.
2- "The average of the last three years": ie the average number of persons per month for the last three years. e.g. 201202 should be = (15(200902) +8(201002) +25 (201102)) / 3, see the attached Excel file.
Sorry I confused you!
It is too heavy for the chart and it takes a long time to be updated after each selection so I thought that maybe it's better to have them calculated already in the script.
Sorry Sunny if I confused you.
But could you help me with the script equations?
Many thanks in advance!
I will try to work on this over the weekend.... Probably won't get time to see this before that because of the complexity involved.
Hi Sunny, hope I'm not interrupting. I wonder if you've had time to look at my question?
Thanks in advance!
Sorry I totally forgot about this.... checking it again (will have to remind myself what we were doing here)
Added two new Concatenations to the AsOfPeriodTable table
CONCATENATE (AsOfPeriodTable)
LOAD
Period as AsOfPeriod,
'PREV 1 Rolling 12' as PeriodType,
date(AddYears(addmonths(Period,1-iterno()), -1),'YYYYMM') as Period1,
Year(Period) as Year,
Month(Period) as Month
RESIDENT PeriodTbl
WHILE iterno() <= 12;
CONCATENATE (AsOfPeriodTable)
LOAD
Period as AsOfPeriod,
'PREV 2 Rolling 12' as PeriodType,
date(AddYears(addmonths(Period,1-iterno()), -2),'YYYYMM') as Period1,
Year(Period) as Year,
Month(Period) as Month
RESIDENT PeriodTbl
WHILE iterno() <= 12;
and then the Three-year average for the previous 12 months rolling expression will be this:
(Count(DISTINCT{<PeriodType={'PREV 1 Rolling 12'}>}IDPerson) + Count(DISTINCT{<PeriodType={'PREV 2 Rolling 12'}>}IDPerson))/2
Is this expression slow too?
(Above(count(DISTINCT IDPerson), 12) + Above(count(DISTINCT IDPerson), 24))/2
This seems to be more dynamic and might be kept as is unless you really want to push this to backend also.
Best,
Sunny