How to choose a calculation of field C, when field A is actually field B?

[sersal10]

Hi all,

I have a table loaded from a database with fields such as event_code, previous_event_code, event_name, category, and quantity.

I want to display the different categories of sold tickets of the previous event in a table on my dashboard.

So the tricky thing is that when I filter by an event_name, I will have the categories of the tickets of this year displayed in a table, but want to display the categories of the tickets of the previous_event_code. So if I filter and chose event_code = 'ER_2024_3' those rows will have a previous_event_code =' ER_2023_1' and need to display the categories of tickets when the rows of the whole dataset has ' ER_2023_1' as event_code and not as previous_event_code.

To do this, I create a table in the dashboard with a column called

category, another column with

sum(quantity), and another column with this expression:

Sum({<year_event=, month_event=, event_code=p(previous_event_code), event_to_compare=, event_name= >} quantity).

However, the categories of the previous event are all showing up as null. In the dashboard, I filter by event_name.

I think I need a set analysis that says when event_code is actually previous_event_code.

Is it possible to do it in Qlik via expression, or maybe I need to do something in the load editor, or actually I need to transform the data before Qlik?

Many thanks. Any help much appreciated.

[igoralcantara]

Hey,

This is an interesting problem. Try to modify your expression to something like this:

Sum({<year_event=, month_event=, event_code={"=previous_event_code"}, event_to_compare=, event_name= >} quantity)

In this expression, I replaced `event_code=p(previous_event_code)` with `event_code={"=previous_event_code"}` to specify that you want to sum the quantity where the event_code matches the previous_event_code. This should ensure that you're correctly filtering the data to display the categories of tickets sold for the previous event when filtering by the current event.

Let me know if this works.

[sersal10]

Hi Igor, 

I tried it but it says there is 0 of each category including the nulls. with the previous approach at least detected the number of quantity but was not distributed properly as it said all where of category null.

Thank you for your answer.

[sersal10]

Hi, I don't know what has happened but it was OK using this: 

Sum({<year_event=, month_event=, event_code=p(previous_event_code), event_to_compare=, event_name= >} quantity).

So false alarm and thank you for your answer again.

[igoralcantara]

ok, that is cool. I am glad that it worked.