Sum of distinct ID based on matched value in another field

[tas_taba1]

Hello Qlikkers,

I have a dataset like the following-

 The field cinema has multiple values for the same PersonID. Now, I have to calculate some measures in QS application based on values chosen from Cinema filter-

1. Now, if someone chooses more than one  value  from the Cinema filter where the PersonID and UtilityWt is same then in the calculation, the UtilityWt has to be counted just once. For example, if someone chooses the values- Inception, Interstellar and Interstellar/Inception/Tenet from the cinema filter the measure Sum(UtilityWt)  should give a result of 185 (80+105).

2. I also need to create a category based on the chosen cinema values for calculating other measures. For example, if someone wishes to see the statistics for the cinemas- Inception and Interstellar in a table, then a temporary variable has to be created. I used the expression 'If(OR(Cinema="Inception", Cinema="Interstellar"), TRUE, FALSE)'  to create the field chosen cinema in the excel file. I have to create this in the front end visualization since the value stored in the variable will be required to calculate other measures.

I am really looking forward to any help regarding the matter.  I am also attaching the dummy dataset and sample app for convenience. Thank you.

[Ksrinivasan]

hi,

ksrinivasan