topic Re: Relative expression of a calculated dimension in QlikView

Relative expression of a calculated dimension

flo2 — Wed, 25 May 2022 10:29:46 GMT

Hello All,

I was wondering how to calculate a relative expression with a calculated dimension.

My graph :

( in this example the last column doesn't work, it should have as total 100%

My column age is an age range column and is based on multiple if ( to define kind of age range thanks to the dimension age defined in the script ). [ for example if age >50 and < 54 so "50-54',if.... ]

To make the relative column, I thought about using the " TOTAL " keyword ( with the sum )

so something like

num( (sum({<Year={$(=(2018))}>} [number]*number2)) ,'# ##0,0',',',' ')/num( (sum(TOTAL {<Year={$(=(2018))}>} [number1]*number 2)) ,'# ##0,0',',',' ')

But as i have an calculated dimension i can't put a name of the dimension inside the < > after the keyword TOTAL . So I think that's why it doesn't work.

am I forced to put the calculated dimension in the script so i have a true dimension name i can put with the total keyword ?

Thanks for reading

Re: Relative expression of a calculated dimension

marcus_sommer — Wed, 25 May 2022 11:04:09 GMT

Yes, in TOTAL or within aggr() native fields are needed. Creating them within the script is most often much easier as creating these logic within the UI and will of course speed up the performance.

- Marcus

Re: Relative expression of a calculated dimension

flo2 — Wed, 25 May 2022 12:27:00 GMT

Ah thanks, indeed ! Also I have another question which follows in case 🙂

I created a new field which contains the " age range" but in the expression i would need to suppress all the values who are not "50-54" or "54-59" or ">=60"

But when i put in set analysis the set analysis modifier ">=60", it is interpreted as if I asked all the values superior than 60 and not as if i asked the exact text value">=60"

my set analysis : num( (sum({<[age range]={'50-54,'55-59,'>=60 '}>} [field1]*field2)) ,'# ##0,0',',',' ')

I thought it was only when i put the dollar sign $ that i was interpreted as all values superior than

Did I do it wrong ?

thanks again !

Re: Relative expression of a calculated dimension

marcus_sommer — Wed, 25 May 2022 12:35:22 GMT

The listing of these string-values will work if they are properly spelled and the quotes are correctly set. You may try it with:

num(sum({<[age range]={'50-54', '55-59', '>=60'}>} [field1]*field2) ,'# ##0,0',',',' ')

- Marcus

Re: Relative expression of a calculated dimension

flo2 — Wed, 25 May 2022 12:41:29 GMT

I correctly spelled them and it doesn't work 😕

i am going to try to create a little app to see if has the same problematic or not

Re: Relative expression of a calculated dimension

marcus_sommer — Wed, 25 May 2022 12:46:24 GMT

Which results do you get - zero or as if no conditions were applied? The last could be caused if the field itself isn't correctly set. Above you say it's " age range" and within the expression it's called [age range] - which is not the same. The first one has an extra leading space.

- Marcus

Re: Relative expression of a calculated dimension

flo2 — Wed, 25 May 2022 13:22:39 GMT

it is like if there was no condition ( no analysis modifier ) if i have in set analysis modifier "<25" or ">=60" ( when there are no < or > in the text value, no problems, my set analysis modifier works perfectly ) and I did the same things for 2 other apps with inline data in script and on these apps there are no problems i don't understand...

It's like if there was a character i can't see just before or after the > or < but I copy / paste the value of the data so it should work

Weird problem because it totally works except in the app where i work ahah

Thanks for help, now i know it's specifically on my app and i will search why

Re: Relative expression of a calculated dimension

marcus_sommer — Wed, 25 May 2022 13:24:38 GMT

">=60" is not querying a string else applying a numeric comparison. To make a string-query it should be: '>=60'

- Marcus

Re: Relative expression of a calculated dimension

flo2 — Wed, 25 May 2022 13:26:10 GMT

Yes the " " was just bad text in my response 😁