Hi,
@OmarBenSale helped me with figure out an expression in an earlier post which has this function:
When id and dates are identical, and the names starts with "b*" and "c*", then only sum rows where name starts with "c".
The expression is this:
if(index(concat (distinct total <Id,Date> Name),'b*')>0 and
index(concat (distinct total <Id,Date> Name),'c*')>0,sum({<Name={'c*'}>}Value),sum(Value))
And the data is like this:
| Id | Date | Name | Value | Sum |
| 1 | 10-10-2020 | a* | 1 | 1 |
| 2 | 11-10-2020 | b* | 2 | 2 |
| 3 | 12-10-2020 | b* | 3 | 0 |
| 3 | 12-10-2020 | c* | 4 | 4 |
| 4 | 13-10-2020 | a* | 5 | 5 |
| 4 | 13-10-2020 | b* | 6 | 6 |
@rubenmarin showed me how to the calculation in script: https://community.qlik.com/t5/New-to-Qlik-Sense/calculated-measure-in-script/m-p/1811557#M185856
My datamodel is more complicated than the test data. And I have a hard time getting the "script way" to work. I have expanded the expression from my first post and it sort of works. But it only works in a straight table and when the fields ID and Date are added to the table. If i filter to specific colours, then it wont work, because it displays all rows for that colour. And also for some reason it counts -1 instead of 1??
Can I somehow use this expression for a count of the total in the frontend (use in graphs and other visualisation) ?
The expression is like below. I cant make up so many different colours, so i have used numbers 🙂
=(if(index(concat (distinct total <,date> Colour),'1')>0 and
(index(concat (distinct total <,date> Colour),'2')>0
or index(concat (distinct total <,date> Colour),'3')>0
or index(concat (distinct total <,date> Colour),'4')>0
or index(concat (distinct total <,date> Colour),'5')>0
or index(concat (distinct total <,date> Colour),'6')>0
or index(concat (distinct total <,date> Colour),'7')>0
or index(concat (distinct total <,date> Colour),'8')>0
or index(concat (distinct total <,date> Colour),'9')>0
or index(concat (distinct total <,date> Colour),'10')>0
or index(concat (distinct total <,date> Colour),'11')>0
or index(concat (distinct total <,date> Colour),'12')>0
or index(concat (distinct total <,date> Colour),'13')>0
or index(concat (distinct total <,date> Colour),'14')>0
or index(concat (distinct total <,date> Colour),'15')>0
or index(concat (distinct total <,date> Colour),'16')>0
or index(concat (distinct total <,date> Colour),'17')>0
or index(concat (distinct total <,date> Colour),'18')>0
or index(concat (distinct total <,date> Colour),'19')>0
or index(concat (distinct total <,date> Colour),'20')>0
or index(concat (distinct total <,date> Colour),'21')>0),
sum({<Colour={'2','3','4',
'5',
'6',
'7',
'8',
'9',
'10',
'11',
'12',
'13',
'14',
'15',
'16',
'17',
'18',
'19',
'20',
'21',
}>}value),sum(value))
and
if(index(concat (distinct total <,date> Colour),'22')>0 and
index(concat (distinct total <,date> Colour),'23')>0,
sum({<Colour={'23'}>}value),sum(value))
and
if(index(concat (distinct total <,date> Colour),'24')>0 and
index(concat (distinct total <,date> Colour),'23')>0,
sum({<Colour={'23'}>}value),sum(value))
