topic Re: Calculated dimensions in QlikView

Calculated dimensions

jillwoodley — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculated dimensions

shraddha_g — Thu, 08 Mar 2018 07:29:58 GMT

Where are you trying this in? Qlikview or Qliksense?

Re: Calculated dimensions

jillwoodley — Thu, 08 Mar 2018 08:34:08 GMT

Sorry Qlik sense

Re: Calculated dimensions

marcus_sommer — Thu, 08 Mar 2018 12:45:07 GMT

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

Re: Calculated dimensions

jillwoodley — Thu, 08 Mar 2018 14:18:45 GMT

Thanks Marcus.

I saw this post but just can't seem to get the syntax right?

Re: Calculated dimensions

shraddha_g — Thu, 08 Mar 2018 14:22:18 GMT

can you share sample data or sample app to try with?

Re: Calculated dimensions

marcus_sommer — Thu, 08 Mar 2018 14:24:46 GMT

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

Re: Calculated dimensions

jillwoodley — Mon, 19 Mar 2018 17:43:37 GMT

Thank you Marcus - it works

Sorry it has taken me a while to respond but i've been on holiday.