topic Re: 'Cross measure' calculation in qlik in App Development

'Cross measure' calculation in qlik

JohnIssue — Fri, 15 Nov 2024 21:13:29 GMT

Hello everyone;

I have a data model that seems to be unchangeable and a calculation problem that I present as follows.

Let's imagine we have a table like this:

Year Country Store Code

2021 Spain 1

2021 France 2

2020 Spain 2

The situation is as follows. As you can see, the stores are related by their code, but they can change the country of management. I want to have in the same row in a table the country and two measures, the number of stores for a specific year, and the number of stores for the previous year. However, the relationship must be made through the code, i.e., if I study France, I should see that in 2021 there was one store and in 2020 another, as the country hierarchy should be that of the year being studied. Is there a way to achieve this? Thank you all very much!

Re: 'Cross measure' calculation in qlik

Or — Tue, 28 Nov 2023 15:22:56 GMT

Luckily for us all, this exact issue has been written up by HIC:

https://community.qlik.com/t5/Design/Slowly-Changing-Dimensions/ba-p/1464187

Note that in your case, it may be enough to simply link the tables by both Year and Store Code. You may need to fill in the missing values for years in that case.

Re: 'Cross measure' calculation in qlik

JohnIssue — Tue, 28 Nov 2023 15:44:18 GMT

Hi! Thank you for your answer but I dont really understand it 😞

In my case I have an unique table and I want to use the current country information for previous years.

Re: 'Cross measure' calculation in qlik

Or — Tue, 28 Nov 2023 16:17:50 GMT

If you always want to use the most recent information, you can just load that one without loading any of the others. It should look something like:

Load Code, FirstSortedValue(Country, -Year) as Country

From YourTable

Group By Code;

Re: 'Cross measure' calculation in qlik

JohnIssue — Tue, 28 Nov 2023 17:02:39 GMT

Maybe I ve not explained well myself, but I have several years. For each year I need to show the 'hierarchy' of each year and the information from previous years with each year 'hierarchy'