Calculating index based on the value in Jan every year

[cheryl]

Hi Qliksense community,

I have a pivot table. Row is OrderNr, and ProductItem; Column is Year and Month; Measure is Sum(TotalAmountEUR).

For each month in the pivot table, I would like to have 2 measures:

1. TotalAmountEUR in that month for that order and item

2. TotalAmount EUR in Jan of the corresponding year for that order and item.

So Measure 1/Measure 2 would be the index I want to calculate.

For example, in Feb-2023, it will display 2 measures: Total order amount in EUR in Feb-2023 for that order and item & Total order amount in EUR in Jan-2023 for that order and item; in Mar-2022, it will display 2 measures: Total order amount in EUR in Mar-2022 for that order and item & Total order amount in EUR in Jan-2022 for that order and item.

What formula should I write in the expression to get Measure 2?

Thanks,

Cheryl

[MayilVahanan]

Hi

Try like below

Sum(Total<OrderNr, ProductItem, Year> {<Date=, Year=, Month={'Jan'},MMMYY=>} TotalAmountEUR)