topic Re: Load Inline Table Formula Help in App Development

Load Inline Table Formula Help

pgalvezt2021 — Wed, 13 Aug 2025 00:02:40 GMT

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!!!!

Re: Load Inline Table Formula Help

Amit_Prajapati — Wed, 13 Aug 2025 04:49:26 GMT

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

Re: Load Inline Table Formula Help

Kaushik2020 — Wed, 13 Aug 2025 08:50:05 GMT

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 🙂

Re: Load Inline Table Formula Help

Kaushik2020 — Wed, 13 Aug 2025 08:51:54 GMT

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)

Re: Load Inline Table Formula Help

pgalvezt2021 — Wed, 13 Aug 2025 13:57:34 GMT

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.

Re: Load Inline Table Formula Help

howdash — Wed, 13 Aug 2025 16:33:02 GMT

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?

Re: Load Inline Table Formula Help

hanna_choi — Thu, 14 Aug 2025 04:16:07 GMT

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)

Re: Load Inline Table Formula Help

pgalvezt2021 — Thu, 14 Aug 2025 12:59:18 GMT

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.

Re: Load Inline Table Formula Help

pgalvezt2021 — Thu, 14 Aug 2025 13:16:15 GMT

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.

Re: Load Inline Table Formula Help

pgalvezt2021 — Thu, 14 Aug 2025 13:17:50 GMT

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.

Re: Load Inline Table Formula Help

howdash — Thu, 14 Aug 2025 14:21:21 GMT

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.

Re: Load Inline Table Formula Help

pgalvezt2021 — Thu, 14 Aug 2025 15:23:39 GMT

Hi,

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.

Hopefully that makes sense. Thanks!!!

Re: Load Inline Table Formula Help

howdash — Thu, 14 Aug 2025 19:35:11 GMT

Video

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.

Re: Load Inline Table Formula Help

pgalvezt2021 — Thu, 14 Aug 2025 19:49:20 GMT

Hi!!!!

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!!!