Hi @NormanStanleyBadger
Take a look at this blog post, it describes solving these kinds of problems in the load script:
https://www.quickintelligence.co.uk/qlikview-accumulate-values/
What you can do is create a new dimension that links each value to its prior month.
The table would need to look like this:
| CompType |
Month |
CompMonth |
| Current |
May-2023 |
May-2023 |
| Prior |
May-2023 |
Apr-2023 |
| Current |
Apr-2023 |
Apr-2023 |
| Prior |
Apr-2023 |
Mar-2023 |
Where Month is the month field in your existing table and the CompMonth field associates it with it's prior month.
You would build this table in the load script, after loading all other data, like this:
CompMonths:
LOAD DISTINCT
'Current' as CompType,
Month,
Month as CompMonth
RESIDENT YourDataTable;
LOAD DISTINCT
'Prior' as CompType,
Month,
Date(AddMonths(Month, -1), 'MMM-YYYY') as CompMonth
RESIDENT YourDataTable;
You would then use the CompMonth dimension in your table or chart and a simple sum(Value) would give you two months worth of data on each dimension point (due to CompMonth associating with two months).
You can then use set analysis to get the Current Month or Prior Month values, with these expressions:
sum({<CompType*={'Current'}>}Value)
and
sum({<CompType*={'Prior'}>}Value)
The variation to the prior month can be found like this:
(sum({<CompType*={'Current'}>}Value)-sum({<CompType*={'Prior'}>}Value))/sum({<CompType*={'Prior'}>}Value)
Approaching it this way makes it simple, but the selections do get a bit messed up. You can expand the set anlaysis to get around this and have Month=,CompMonth=P({$}Month) in your set analysis.
You do need to ensure that all months are formatted the same (using the Date function) to keep things moving smoothly.
Hope that helps.
Steve
