If Then Else error

[gfisch13]

Hi - after completing this conditional statement and using it in one of my charts I realized I made an error and do not get my desired result..

Statement:    IF([CC_MCC_Desc] = 'Grocery Stores and Supermarkets', Sum([CC_ITEM_COST]), Sum([CC_Tran_Amt]))

this statement works in the chart only when the value 'Grocery Stores and Supermarkets' is selected In my CC_MCC_Desc list box.

what I really need to do is have it function all the time aeven when this specific selection isn't made since there is a mixture of records.   What did I do wrong?

[simrankaur]

Try this:

IF([CC_MCC_Desc] = 'Grocery Stores and Supermarkets', Sum({<[CC_MCC_Desc] = {'Grocery Stores and Supermarkets'}>}[CC_ITEM_COST]), Sum([CC_Tran_Amt]))

[jaibau1993]

Hi!

In your expression, when you write the condition [CC_MCC_Desc] = 'Grocery Stores and Supermarkets', it is equivalent to Only([CC_MCC_Desc]) = 'Grocery Stores and Supermarkets'. This is because in an expression you must aggregate data using agregation functions (like sum, count, max, only...). Thus, if you do not use agregation functions Qlik uses Only as default. 

Said so, what is the logic of your expression? I mean, if nothing is selected on CC_MCC_Desc, how should it behave?

Regards,

Jaime.

[tresesco · Accepted solution]

May be this:

IF(Max(Match([CC_MCC_Desc], 'Grocery Stores and Supermarkets'))=1, Sum([CC_ITEM_COST]), Sum([CC_Tran_Amt]))

This expression would work (with/without selection) in checking of that specific value in the field.

[jaibau1993]

Hi Tresesco!

Will it work? I don't get the logic. Is Match evaluated for each CC_MCC_Desc record without the need of Aggr function?

As equivalent to what you purpose, I would have write something like

If( Index(Concat(CC_MCC_Desc), 'Grocery Stores and Supermarkets'), then, else)

Regards,

Jaime.

[tresesco]

Hi Jaime,

Aggr function is there (max) in my expression; you are using index outside it, I am using using it inside. Both will work. Give a try in a sample data to find a specific string/value. 🙂

[jaibau1993]

Hi Tresesco:

What I did not know (and it is what confused me) is that Match returns a list of values when used as you did:

Nice lesson learned today!

Regards,

Jaime.

[tresesco]

This is nothing special about match() - I would say. It's rather because of aggregation function you use outside. The similar behavior you would see with other functions as well. Here index() would be similar utility case. I.e. - you can use index() in exact similar fashion.

[gfisch13]

Thank you all for the learning opportunity!