Using Set Expression with Conditional IF Function

[Schdeve]

Hey everyone,

I'm pretty new to Qliksense and have been struggling with the Set Expression Formula for some time now.

Right now I'm working on an app that contains information about tickets coming into customer service (across 3 Dimensions: Month, Market & Channel) that are allocated to specific Groups (2 Dimensions: Department & Team) within the company. On the other hand, the app also contains information about overall purchases (also split on the 3 Dimensions: Month, Market & Channel). I have now compiled a Metric that gives me the number of tickets per purchases on the basis of the 3 Dimensions: Month, Market & Channel) as well as the same Metric for the Number of Tickets per Purchase allocated to specific Departments/Teams.

This is now where I'm running into a couple of issues: I have been using the following formula to calculate this share for the respective Departments & Teams (ignoring the department/team split in the purchase data as it does not factor into the purchases and should be ignored) which has worked well:

SUM(number_tickets*ticket_value) / SUM(TOTAL <department_name , team_name> purchases)

Now I would also like to factor in a conditional IF Statement however to account for the fact that only a specific time-frame should be considered in the calculation (the ticket & purchase data have slightly miss-matching time frames which I believe all get summed up regardless when using the "Total" indicator). I cannot figure out however how to combine the IF statement within the Set Expression Function that should calculate the purchase data which is why I am using this half-done formula at the moment and was hoping to get input from others about how to account for the same time frame in the denominator of this function: 

SUM(IF(month>='2019-01' AND month <= '2019-086, number_tickets*ticket_value)) / SUM(TOTAL <department_name, team_name> purchases)

Thank you in advance!
Best,

Steve

[dwforest]

Assuming each ticket has a unique identifier, TicketID
SUM(${<TicketID={"=month>=date#('2019-01','yyyy-mm') and month <= date#('2019-08','yyyy-mm')"}>} number_tickets*ticket_value)) / SUM(TOTAL <department_name, team_name> purchases)
basically stick with Set Analysis, mixing with IF gets confusing IMHO
Note that date comparisons can be tricky due to the dual nature of dates (number, string display); i've had the best luck explicitly converting to a number in Set Analysis
Could also try:
SUM(${<{month=(">=date#('2019-01','yyyy-mm') <= date#('2019-08','yyyy-mm')"}>} number_tickets*ticket_value)) / SUM(TOTAL <department_name, team_name> purchases)

[Schdeve]

Hey, this sounds really good already - thanks for your input! 🙂

The issue however is that the tickets don't have a unique id but are just summed up across the different categories (of issues) that they fall into - is there any way to do it regardless of this or would it be best to add an additional column with a TicketID instead somehow?

Thanks!