I would like to know if you can help me find the formula that calculates the maximum periods per product, regardless of which period I select in the filter. For example:
Example if I choose 202502 202502 D 202311 C 202203 B 202104 A
Example if I choose 202203 202202 D 202001 C 202203 B 202104 A
Example if I choose 202008 201805 D 202001 C 202008 B 202007 A
Example with no period selected
202507 D 202511 C 202504 B 202404 A
And when this is done, I want the total sum of those maximum periods to appear.
I see. So you want the selections to be ignored but not always. It's kind of like using the period field to specify the the maximum period limit. That makes sense.
What values do you expect to see for A, B, and C when 201805 is selected? A, B, and C don't have a period that is less than or equal to 201805.
Rule 1: If I do not select any period as a filter, the table should display the maximum periods per code. Rule 2: If I select any period, Qlik should display in the table the maximum period according to the selection I made for the corresponding code, and also the other codes with their maximum periods.
For 😧 201805, 681. And nothing else.
But imagine there is a code A with two periods, 201801 and 201802. Qlik should display D with value 681 and A with value X for 201802.
Solution is below and the video demoes the solution and how it works.
Solution
As for the solution, try this 👇
Script
// load data and create a field to map Totals to Product and Period
TempData:
LOAD *,
AutoNumber(Cod_Product & '|' & Period) as key
INLINE [
Cod_Product, Period, Total
A, 202004, 675
A, 202007, 646
A, 202104, 668
A, 202404, 570
B, 202108, 901
B, 202008, 955
B, 202504, 597
B, 202203, 682
C, 202511, 135
C, 202311, 551
C, 202510, 292
C, 202001, 867
D, 202502, 912
D, 202202, 126
D, 201805, 681
D, 202507, 406
];
mapProductPeriodToTotal:
Mapping
Load key,
Total
Resident TempData
;
// loop trough each period to find max Period per product
For Each vPeriod in FieldValueList('Period')
maxValues_temp:
Load '$(vPeriod)' as Period,
Cod_Product as MaxCod_Product,
Max(Period) as MaxPeriod
Resident TempData
Where Period <= $(vPeriod)
Group By Cod_Product
;
Next vPeriod
// map Totals to product and Max Period within a period
maxValues:
Load Period,
MaxCod_Product,
MaxPeriod,
ApplyMap(
'mapProductPeriodToTotal',
AutoNumber(MaxCod_Product & '|' & MaxPeriod),
Null()
) as MaxPeriodTotal
Resident maxValues_temp
Order By Period,
MaxCod_Product
;
Drop Table maxValues_temp;
This will create a table that will have max period and corresponding total for each period and product.
Frontend
On the frontend, add a Filter Pane with Period as the dimension. Then add a table with MaxCod_Product, MaxPeriod, and MaxPeriodTotal dimensions.
When user will select a period, the table will update to show max Period and corresponding Total for selected period.
Limitation
The only thing that this solution won't do is show the max Period and Total per product when no Period is selected. A way to work around that is to enable Always One Value Selected setting for Period field and select a max period. Or have a bookmark that will trigger when an app is opened and automatically select the max available Period for user.
The solution you described in the post is exactly what I was looking for!!! So thank you so much, and I’m glad the requirement was understood, because honestly, at the beginning it was a bit confusing to explain (at least I think so). So, many thanks, howdash!!!
