topic Historical aggregations in QlikView

Historical aggregations

kdmarkee — Fri, 15 Nov 2024 23:39:35 GMT

Is there some way to accomplish the yellow columns in a straight chart or do I have to figure this out in script and my data model instead? The idea is to try to get an average from the same service month but from the prior year to make a prediction as to how many more cases we should release for the actual "current" service month of interest. I can't seem to figure out what my expression/set analysis should look like to generate those expression values for columns G and H based on the date/timeframe in column F. Columns C and F technically are using the same Service calendar in my month. Also, ideally columns G and H would be static and always be the same value for a given Group and Plan combination. Thanks.

Re: Historical aggregations

Marcos_Ferreira_dos_Santos — Tue, 10 Nov 2020 14:37:20 GMT

Hi kdmarkee

It seems that no, you won't have exactly the same chart.

But you do can have all the needed information in a straight chart, if you use the info of column F (same month last year) as a label for columns G and H, and bring the content of those using Set Analysis.

I think this would be the best Qlik solution for your question.

Re: Historical aggregations

kdmarkee — Tue, 10 Nov 2020 22:16:52 GMT

I'm not sure I follow your post, but if you are suggesting I should be able to generate columns G and H from F, it is that very thing that I cannot seem to figure out the correct syntax and set analysis for.

Re: Historical aggregations

edwin — Wed, 11 Nov 2020 02:06:18 GMT

there are two ways of accomplishing this, one is to create a bridge that relates current month with the month 1 year after - i prefer that as the calculation is done in the script and offloads your UI. another is simpler, add a common field across years - which is the month of your , add year of the date:

sample data:

data:
load date(today()-Rowno()*5) as Date, MonthStart(today()-Rowno()*5) as Month, num(Month(today()-Rowno()*5)) as MonthNum, Year(today()-Rowno()*5) as YearNum, rand()*100 as Amount AutoGenerate(365);

create two variables: the current year selected and compute the year after that:
currentYear : =only(YearNum)
nextYear: =currentYear+1

make these variables immediate as you will use it in your set analysis

and in your chart, use MonthNum as your dimension, since it is related to all years, you can display any year in your chart with the same month. then create two calculated dimensions to show current year months and next year's months:
=date(MonthNum&'/1/'&$(currentYear))
=date(MonthNum&'/1/'&$(nextYear))

and expressions:
=sum({<YearNum={$(currentYear)}, Month=>}Amount)
=sum({<YearNum={$(nextYear)}, Month=>}Amount)

this assumes your user selects a Month for example to identify the current year:

Re: Historical aggregations

kdmarkee — Fri, 13 Nov 2020 16:04:07 GMT

Thanks for this idea. I implemented something very similar and, unlike your sample, in order to only show the Month of interest I created a calculated dimension to hide the rows for the other months.