Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the following expression that works in a table as an expression, but when i copy it to a calculated dimension and go to use it in a visualization it says 'invalid dimension'.
Can anyone help me please..
if ((sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount))=(sum ({$< [Year]={'$(vTY)'},[Type]={'CI'}>} Amount))and (sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount)<>0),'=',
if ((sum ({$< [Year]={'$(vTY)'},[Type]={'CI'}>} Amount))>(sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount)) and (sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount)>0),'>','good'))
I'd be really grateful.
Thanks,
Jill
At first check your formula as expression in your table. If it returned the expected results then should the following be working:
aggr(
if ((sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount))=(sum ({$< [Year]={'$(vTY)'},[Type]={'CI'}>} Amount))and (sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount)<>0),'=',
if ((sum ({$< [Year]={'$(vTY)'},[Type]={'CI'}>} Amount))>(sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount)) and (sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount)>0),'>','good')),
Dim1, Dim2)
whereby Dim1 and Dim2 are placeholder for the dimensions in which context the calculation should be applied.
- Marcus
Where are you trying this in? Qlikview or Qliksense?
Sorry Qlik sense
You need to aggregate it with aggr() against your used dimensions. It's a post for QlikView but the general logic is still valid in Qlik Sense: Calculated Dimensions.
- Marcus
Thanks Marcus.
I saw this post but just can't seem to get the syntax right?
can you share sample data or sample app to try with?
At first check your formula as expression in your table. If it returned the expected results then should the following be working:
aggr(
if ((sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount))=(sum ({$< [Year]={'$(vTY)'},[Type]={'CI'}>} Amount))and (sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount)<>0),'=',
if ((sum ({$< [Year]={'$(vTY)'},[Type]={'CI'}>} Amount))>(sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount)) and (sum ({$< [Year]={'$(vTY)'},[Type]={'PR'}>} Amount)>0),'>','good')),
Dim1, Dim2)
whereby Dim1 and Dim2 are placeholder for the dimensions in which context the calculation should be applied.
- Marcus
Thank you Marcus - it works
Sorry it has taken me a while to respond but i've been on holiday.