topic Re: Calculated Dimension not working in QlikView

Calculated Dimension not working

Wed, 13 Sep 2017 11:14:35 GMT

Hi All,

I have a put in a calculated, to work out buckets, need to be dynamic so no script change. The expression is:

=IF(Round(SUM(ChangeIncShareUSD),0.11) >= 500000, '>500k',

IF(Round(SUM(ChangeIncShareUSD),0.11) >= 250000, '>250k to 500k',

IF(Round(SUM(ChangeIncShareUSD),0.11) >= 100000, '>100k to 250k',

IF(Round(SUM(ChangeIncShareUSD),0.11) >= 0, '>0k to 100k',))))

Worryingly it doesn't parse, but when placed as a normal expression it returns correctly just not grouping up.

Any help appreciated.

Re: Calculated Dimension not working

Wed, 13 Sep 2017 11:15:37 GMT

oops missed out the most important word 'dimension' as in calculate dimension.

Re: Calculated Dimension not working

sunny_talwar — Wed, 13 Sep 2017 15:29:25 GMT

You need the Aggr() function for the expression

=Aggr(

IF(Round(SUM(ChangeIncShareUSD),0.11) >= 500000, '>500k',

IF(Round(SUM(ChangeIncShareUSD),0.11) >= 250000, '>250k to 500k',

IF(Round(SUM(ChangeIncShareUSD),0.11) >= 100000, '>100k to 250k',

IF(Round(SUM(ChangeIncShareUSD),0.11) >= 0, '>0k to 100k',))))

, Dimension/s)

Dimension/s would be the dimensions on which you are aggregating your ChangeIncShareUSD

Re: Calculated Dimension not working

Anil_Babu_Samineni — Wed, 13 Sep 2017 15:36:09 GMT

May be one extra comma is there?

=IF(Round(SUM(ChangeIncShareUSD),0.11) >= 500000, '>500k',

IF(Round(SUM(ChangeIncShareUSD),0.11) >= 250000, '>250k to 500k',

IF(Round(SUM(ChangeIncShareUSD),0.11) >= 100000, '>100k to 250k',

IF(Round(SUM(ChangeIncShareUSD),0.11) >= 0, '>0k to 100k',))))

Re: Calculated Dimension not working

Wed, 13 Sep 2017 16:35:17 GMT

Thanks it is now working, just putting an Inline table to order the buckets. Thank you again.