Hi @sunil-kumar5
The first question is whether this flag to say that a partner is active should take into account selections. For instance, if you pick a licence number that is one installation so no one will be able to average >= 1 across three months.
This question dictates whether you are calculating the flag in the load script or front end. I will assume front end.
You need each column of that pivot to be looking at three months, so Mar 20 needs to consider Jan, Feb and Mar 20. The count of installs then needs to be divided by the number of months.
One way of achieving this is to calculate months from all of your Installation Dates in the load script:
[Installation Date],
Date(MonthStart([Installation Date]), 'MMM-YY') as [Installation Month],
You then need to create a subsequent table which has a field, Active Month, that joins to the right Installation Months. So for March you would have:
Active Month Installation Month Active Months
Mar-20 Jan-20 1
Mar-20 Feb-20 1
Mar-20 Mar-20 1
This table needs to be built out for all months to the end of the year, and can be produced in a loop.
This will join to the main table. Using Active Month as the dimension of your pivot you can then do something like to get the average installations per month for a rolling three month period:
sum(InstallationCount) / sum([Active Months])
To count partner codes which average greater than 1 you need to use an aggr function.
sum(aggr(if((sum(InstallationCount) / sum([Active Months])) >= 1, 1, 0), PartnerCode))
I think that should give you what you need.
This blog post goes into more detail on linking periods together to get rolling periods:
https://www.quickintelligence.co.uk/qlikview-accumulate-values/
Hope that helps.
Steve
