topic Re: Filter a variable value in App Development

Filter a variable value

Giuseppe_Alleruzzo — Fri, 23 Feb 2024 09:31:43 GMT

Hi guys, i've a problem. In my project i've many things to sum and all of them give results by year, month and day.

To simplify my scripts i created a variable "v_total_count" with inside it sum(A) + sum(B) + Sum(C) ....+ Sum(Z)

Now I want to create a chart with months as rows and total count for specific years as columns.

How can i do this without filtering dimensions in my dashboard?

For example as first column i'd like to have count month by month for year 2024

if i have an expression i do Sum({$<Year {'2024'}>}A)

What do i have to do to have the same filter for a variable?

Thank you so much

Re: Filter a variable value

Clement15 — Fri, 23 Feb 2024 09:46:22 GMT

Hello,

Have you tried this?

{$<Year = {'2024'}>} $(v_total_count)

Re: Filter a variable value

LRuCelver — Fri, 23 Feb 2024 09:36:01 GMT

You can write the Set Analysis in front of the expression:

{$<Year {'2024'}>} Sum(A)

{$<Year {'2024'}>} $(v_total_count)

Re: Filter a variable value

Giuseppe_Alleruzzo — Fri, 23 Feb 2024 09:41:28 GMT

I tried and into the chart i visualize "Garbage after expression: "$" "

Re: Filter a variable value

Clement15 — Fri, 23 Feb 2024 09:56:30 GMT

What type of chart do you use?

I've got this in a pivot table

Re: Filter a variable value

Giuseppe_Alleruzzo — Fri, 23 Feb 2024 10:03:57 GMT

Straigth table, what do you have into variable v_test?

Re: Filter a variable value

Clement15 — Fri, 23 Feb 2024 10:13:03 GMT

I've got this : sum(FlagLancement)+sum(FlagLancement2)

It's not really useful. I created it for the test.

Re: Filter a variable value

Giuseppe_Alleruzzo — Fri, 23 Feb 2024 10:26:13 GMT

ok thank you, what do you suggest as an alternative?

Re: Filter a variable value

Clement15 — Fri, 23 Feb 2024 10:32:36 GMT

I don't understand the request. Doesn't work on your end? The measure also works in a straight table.

Re: Filter a variable value

Giuseppe_Alleruzzo — Fri, 23 Feb 2024 10:53:37 GMT

I made another test, into variable v_test i put only one sum

Do you know why into the definition of the expression I see "Expression OK" and into the formula all underlined in red, as if there was a mistake?

I don't understand

Re: Filter a variable value

Clement15 — Fri, 23 Feb 2024 10:58:01 GMT

I'm working on the Qlik Sense SaaS version, and I don't have this. I don't know if it's from the version. But be careful not to forget the = in the measure.

Re: Filter a variable value

Giuseppe_Alleruzzo — Fri, 23 Feb 2024 11:11:55 GMT

I think the same thing, it could depend from the version. I work on QV 12. I put the = into the measure but it's not the problem.

What do you suggest as an alternative to the variable if you have to sum 15 dimensions all together to have the total for every day, month or year and use it for another chart?

A,B,C... are different things and have different sources (into the script i have a load for A from a DB, a load for B from another DB...)

I can't write everytime Sum(A) + Sum(B) + ...Sum(X).

Thank you so much for the answer

Re: Filter a variable value

marcus_sommer — Fri, 23 Feb 2024 11:50:01 GMT

AFAIK is the above suggested outer set analysis only within the latest releases of QV12 available. The syntax-highlighter has some restriction but it hints in the direction that your release didn't know the feature already.

Beside this you should check the variable-interpretation independent of the the set analysis - and if it's not working you may change: $(v_test) to $(=v_test)

Further you may include the set analysis as parameter within the variable, maybe in this (simplified) way:

set v = "sum($1 rangesum(A, B,C))"

and then calling the variable like:

$(v({< Year = {2024}>}))

Note: the rangesum() is quite the same like A + B + C without returning an error if any of the values isn't a number and it's not always the same as sum(A) + sum(B) + sum(C) but within the most scenarios it will return the wanted results.

Independent of the above it seems that your measure-fields have a crosstable-structure which is often very painful to handle. Better is mostly to transform the data-set per: The Crosstable Load - Qlik Community - 1468083.

Re: Filter a variable value

Giuseppe_Alleruzzo — Fri, 23 Feb 2024 12:19:37 GMT

Hi Marcus, I tried but filter doesn't work. I created two expressions, the first without filter (so I have totale volumes of 2022,2023 and 2024), and the second with filter as you suggest (2024). The result is the same for the two expressions

I don't know why it doesn't work

Re: Filter a variable value

marcus_sommer — Fri, 23 Feb 2024 12:35:13 GMT

Did you really creates a second variable which contained the mentioned parameter?

Beside this remove the expression-lable and then hoover with the mouse on them. In a popup you will see the applied expressions with a resolving of the variables. Does this expression really look like as if you had written it completely manual?