Historical aggregations

[kdmarkee]

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.

[Marcos_Ferreira_dos_Santos]

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.

[kdmarkee]

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.

[edwin · Accepted solution]

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:

[kdmarkee]

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.