Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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 !
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
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
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
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
">=60" is not querying a string else applying a numeric comparison. To make a string-query it should be: '>=60'
- Marcus
Yes the " " was just bad text in my response 😁