Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
PsyBreaks
Contributor
Contributor

QLIK Sense (sum with the condition)

I need to show the sum of the columns of only those values when the 3 columns combined are unique. Right now my expression looks like this: Num(Sum([myColumn]) , '# ##0,00') I need to add a condition similar to (where Dictinct col1, col2, col3). Can you tell me how to do it? I tried to write like this: Num(Sum(distinct {[col1] + [col2] + [col3]} [mycolumn]), '# ##0.00') , but it didn't help id| col1| col2| col3| mycolumn| \n 1| 0001| 810| yes| 10.00| \n 2| 0001| 810| yes| 10.00| 3| 0001| 840| no| 25.11| 4| 0001| 840| yes| |25.11| 5| 0001| 392| yes| 15.01| 6| 0001| 756| yes| 15.01| total 90,24 col1, col2, and col3 in records with id1 and id2 are equal, so only one of them goes into the sum. In records id3 and id4 col3 are different, so two columns add up. In records id5 and id6 the col2 column is different, so they add up too. Total: 10.00 + 25.11 + 25.11 + 15.01 + 15.01
1 Reply
micheledenardi
Specialist II
Specialist II

I suggest to not create too complex set analysis, it'll make you analysis slower.

Create a new field during load script  like

Load *

If(Col1<>Col2 and Col2<>Col3 and Col1<>Col3,1,0) as OnlyDistinct

From....

 

Then use this field in your expression:

Sum((Col+Col2+Col3)*OnlyDistinct)
or
Sum({<OnlyDistinct={1}>}Col+Col2+Col3)