Set Analysis: Set dimension values equal to another field value

[gbarrett]

Hello,

I am working with a table that contains event ticket data and need help with some set analysis. The goal is to create a line chart that has 1 dimension and 2 measures that compares ticket sales over the course of time for an event (event_id) and its predecessor (py_event_id).  A selection of the event_id must be made for the chart to work. Since the two sale periods could be differing lengths, I have set the dimension to be the percentage of time in the sale period. The syntax is this: NUM(((sale_date-cy_first_sale_date))/(cy_days_in_sale_period), '#,##0.00%'). cy stands for current year. The measure is Sum(tickets). This works in the line chart. I tried creating the second measure with set analysis like this: Sum({$<event=p(event_id)tickets). However, this just returns the same result as the first measure. I am guessing that the set analysis is not affecting the values in the dimension calculation. Does anyone have any insight on how I can make this work? Thank you in advance.

[sarahplymale]

I would suggest using this fantastic set analysis expression generator:

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=‌

I think what you are trying to do will fall under "Indirect Set Analysis".  There are examples and you can enter your field names into the wizard and it creates the correct syntax, which can be tricky if you just type it all up.

[davidpetiot]

Hi George,

I think the reason why your second measure is because once you have made a selection in Qlik (for and event [event_id]), the set of data is filtered, therefore you cannot show the previous event id [py_event_id] as it is outside of the selection.

I'm not sure how your dimension for the sale period links to the event_id so I'm going to assume there is no issue from that perspective.

For the measures to work, you will need to make your selection on something else than the event_id, like a name [event_name].

you will need to create 2 variables

vCurrentID: =Only([event_id]) which returns the event_id from the current [event_name] selection

vPreviousID: =Only([event_id])-1

Here I am assuming that you can find the previous id of the event by applying some logic...

So measure one, for the current selection would be Sum([tickets]) as the event_id is automatically derived from the selection of event name.

The measure for the previous event id would be Sum($<{[event_name]=,event_id={'$(vPreviousID)'}[tickets])

The [event_name]=, part of the expression exclude this particular selection.

You might want to have a look at this if you need to exclude all selections...

https://help.qlik.com/en-US/sense/1.1/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/SetAnalysis....

Hope this helps

David.

[gbarrett]

I don't believe this is the case. The 'p' in my set analysis allows for possible values outside of the selection. I tried your method and the measure still wasn't working.

I believe that the set analysis is properly affecting the measure, but not changing the values in the dimension to the possible values. I just don't know how to get that to work. If I could use set analysis throughout the calculated dimension, I feel like that would work. But I don't believe Qlik has the functionality for that.