Load Inline Table Formula Help

[pgalvezt2021]

Hello,

I have an inline table with the following data: Product Code, Period, and Total.

TempData:
LOAD*
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
];

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 attach an Excel file for better understanding.

Thank you!!!!

[Amit_Prajapati]

Hi Just try with below one

TempData:
LOAD * 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
];

MaxPeriodPerProduct:
LOAD
Cod_Product,
Max(Period) as Max_Period
RESIDENT TempData
GROUP BY Cod_Product;

JOIN (MaxPeriodPerProduct)
LOAD
Cod_Product,
Period as Max_Period,
Total as Max_Total
RESIDENT TempData;

MaxPeriodTotalSummary:
LOAD
Sum(Max_Total) as Total_Max_Periods
RESIDENT MaxPeriodPerProduct;

I'm getting below output for the period 202502

[Kaushik2020]

Hi @Amit_Prajapati  we should see something like below.

202502	D	912
202311	C	551
202203	B	682
202104	A	668

we need to tell chatGpt for a clear answer 🙂 

[Kaushik2020]

if you are using a table then use

dimension as cod_Product || Measure Period :  =Max(TOTAL <Cod_Product>{<Period = {"<=$(=Alt(Only(Period), Max(TOTAL Period)))"}>} Period)

[pgalvezt2021]

Hello, thanks for the response. I think there might be a mistake, could it be? Look: if I don’t filter anything, it should show for 202507 and code D the number 406. D, 202507, 406 for C = 135, etc.
Now, if I filter July 2025, it should show July 2025 for D = 406, for C = 551, for B = 597, and for A = 570.
This is because, according to what I’m filtering, it should return the maximum periods for each code. Since I filtered July 2025, October and November 2025 and the codes associated with those periods should no longer be considered.

[howdash]

Your question and examples seem to contradict each other.

You asked how you can calculate "maximum periods per product, regardless of which period I select". In other words, no matter what period you select, the maximum period per product should always be shown.

The maximum period for product A, for example, is 202404. So no matter what period you select product A should always show 202404.

However, in the examples you select periods and different period (not maximum) is shows for each product. The examples then suggest that period selections are considered.

Which one are you looking for? For selections to be considered or not considered?

[hanna_choi]

Hello @pgalvezt2021 

How about this ways?

when you are selected one values from filter panel, the table values is changed.

I am using the max (), getfieldselections() functions and vPeriod variable.

vPeriod : GetFieldSelections(Period)

[pgalvezt2021]

Hello,

The maximum period for product A, for example, is 202404. So, regardless of which period you select, product A should always display 202404.
R: Everything is correct as you mentioned. However, for example, if I select 202401, then 202404 would no longer be the maximum period, and therefore A should return the maximum period corresponding to 668, but the formula gives 675 instead. In the Excel file, I show you the example.

[pgalvezt2021]

Hi, thanks for your response!!! Let me explain: For the filter you applied, Qlik should show the following:

Filter: 202502

• For code A, it should display 570, as it is the maximum period for A according to the current selection, which is 202502.

• For code B, it should display 682, corresponding to period 202203.

• For code C, it should display 551, corresponding to the maximum period according to your filter, which would be 202311.

• For code D, it should display 912, as it corresponds to the maximum period based on your selection.

[pgalvezt2021]

Hi, I forgot to mention that the periods shown by the formula are correct according to the filter I apply; the issue is that it does not display the totals associated with those maximum periods.