Calculations in set expressions

[Nick_Altis_Trial]

Hi,

I am new to Qlik Sense and i have a question about whether it's possible to perform calculations inside of a set expression or whether there is a better process for something like this.

I have data on values per state and want to count distinct states within a country where there are more than 200 new cases since a date.

Seems the below syntax is not valid but i'm not sure how to go about it another way.

COUNT({<country = 'America',_date="<=30/08/2020",SUM(new_cases)='>=200'>} Distinct state)

[marcus_sommer · Accepted solution]

You may try it in this way:

COUNT({<country = {'America'}, _date = {"<=30/08/2020"}, state = {"=SUM(new_cases)>=200"}>} Distinct state)

- Marcus

[Nick_Altis_Trial]

Thanks for the reply Marcus. 

I tried the expression and it's close, not quite giving the correct answer. i'm not exactly sure how the ---state = {"=SUM(new_cases)>=200"}--- part is calculated by Qlik but after some tests seems that the expression returns all distinct states where SUM(new_cases) >= 200 and somehow excludes the date function (possibly that's just a coincidence though and it's calculating something else) 

[marcus_sommer]

If the sum() should respect any other condition they need to be included there like:

COUNT({<country = {'America'}, _date = {"<=30/08/2020"},
                     state = {"=SUM({< _date = {"<=30/08/2020"}>} new_cases)>=200"}>} Distinct state)

- Marcus

[Nick_Altis_Trial]

Ah ok thanks for clarifying - in this case it seems that the nested [ " "] search causes the syntax to break. e.g. the date search: - date = {"<=30/08/2020"} - fall within the sate search: - state = {"=SUM(.... new_cases)"}.

to clarify it looks like the state search is:  - state = {"=SUM({< _date = {" <-- stopping here.

[marcus_sommer]

Most likely it failed because of the formatting which means that 30/08/2020 isn't interpreted as a valid date or it's just treated as a division-statement.

In this case you may try it with:

... date = {"<='30/08/2020'"} ...  

or maybe with:

...  date = {"<=$(=makedate(2020,8,30))"} ...

The possibilities that things go wrong with formatted fields and/or that they need more or less extra efforts to prepare them before doing any calculations/matchings are quite huge - therefore the general recommendation to avoid them and using only pure numerical fields/values for all calculations/matchings. This doesn't mean that you couldn't have formatted fields within the UI for selections or as dimensions within the charts else that that they are created twice/thrice - as strings + dual() + pure numeric (not all fields - only those which are used for matchings/calculations or which have a special purpose).

- Marcus  

[Nick_Altis_Trial]

Hi Marcus,

Sorry for the delayed response here.

Managed to use a workaround but agree that there is likely some small formatting issue with dates or another field.

However in response to my question more broadly,  setting --- state = {"=SUM(new_cases)>=200"} ---works as a way to include this type of logic in the set expression. 

Thanks very much for your help!

Nick