Getting a correct total count for a multiple if calculations

[Qliksense_77]

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))