topic Re: Calculated Dimensions in QlikView

Calculated Dimensions

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

Hi All,

I have a bar chart wich has a dimesions 'Product' and expression as Count(distinct ID) and label for this expression is "Counts"

I have to add a calculated Dimension to and show three buckets HIgh, Modareat and low , depending on the count .

for which iam trying to do the below add calculated dimension

Under Add Calculated Dimension:

=if(count(distinct ID )>=$(vPercentage) and if(count(distinct ID <=$(vPercentageThirty),'High'),

if(count(distinct ID )>=$(vPercentageThirty) and if(count(distinct ID <=$(vPercentageFourthy),'Moderate','LOW')

But this is not working .

Can someone suggest me what expression do i need to add ?

Thanks

Re: Calculated Dimensions

jagan — Fri, 18 Mar 2016 06:42:37 GMT

HI,

Try ValueList() - synthetic dimension for this instead of using Calculated dimension. Check this links below

Missing Manual - ValueLoop() & ValueList()

Use Case for ValueList Chart Function

Regards,

Jagan.

Re: Calculated Dimensions

avinashelite — Fri, 18 Mar 2016 06:42:40 GMT

Try like this

=if(AGGR( count(distinct ID ), Product)>=$(vPercentage) and if(AGGR( count(distinct ID) ,Product)<=$(vPercentageThirty),'High'),

if( AGGR(count(distinct ID ),Product)>=$(vPercentageThirty) and if(AGGR( count(distinct ID ),Product)<=$(vPercentageFourthy),'Moderate','LOW')

Re: Calculated Dimensions

Kushal_Chawda — Fri, 18 Mar 2016 06:50:45 GMT

try this

aggr(if(count(distinct ID )>='$(vPercentage)' and count(distinct ID) <='$(vPercentageThirty)','High',

if(count(distinct ID)>'$(vPercentageThirty)' and count(distinct ID) <='$(vPercentageFourthy)','Moderate','LOW')),Product)

Re: Calculated Dimensions

smilingjohn — Fri, 18 Mar 2016 07:09:04 GMT

Hi Avinash it is giving Error

Re: Calculated Dimensions

Fri, 18 Mar 2016 07:16:39 GMT

Hi,

Can you share model data set so that the requirement is more clear.

Reshma.

Re: Calculated Dimensions

Kushal_Chawda — Fri, 18 Mar 2016 07:16:41 GMT

Have you tried my expression?

Re: Calculated Dimensions

avinashelite — Fri, 18 Mar 2016 08:36:06 GMT

Sorry Typo error from my end ..try the below expression ...

=if(

AGGR( count(distinct ID ), Product)>=$(vPercentage) and AGGR( count(distinct ID) ,Product)<=$(vPercentageThirty),'High',

if( AGGR(count(distinct ID ),Product)>=$(vPercentageThirty) and AGGR( count(distinct ID ),Product)<=$(vPercentageFourthy),'Moderate','LOW'

)

Re: Calculated Dimensions

jonathandienst — Fri, 18 Mar 2016 08:57:27 GMT

I don't think you need an Aggr() expression, but I was not clear on the relationship between the levels and the two variables as your expression had syntax errors. Assuming the variables are values, then you don't need the expansions either. So if Low means the lowest count values, then perhaps you mean:

=If(count(distinct ID) < vPercentageThirty, Low

If(count(distinct ID) < vPercentageFourthy, 'Moderate',

'High'

)

Re: Calculated Dimensions

Fri, 18 Mar 2016 09:26:44 GMT

I think you are missing ')' in your expression [in the second part after 'and' count distinct ID...]:

=if(count(distinct ID )>=$(vPercentage) and if(count(distinct ID <=$(vPercentageThirty),'High'),

if(count(distinct ID )>=$(vPercentageThirty) and if(count(distinct ID <=$(vPercentageFourthy),'Moderate','LOW')