topic Re: Calcualted dimension in QlikView

Calcualted dimension

Wed, 25 Nov 2020 16:16:04 GMT

I have created a sum of invoice total per customer using this expression

Sum({<[Bill Date]={">=$(=MonthStart(Today(),Day(Today())=1))"} ,
[LinkId] = {"FIN"} >}
[Bill Value] * $(RevenueRate))

What I need to do this group the customers based on their invoice total

I summary if their invoice total is less than 10001 then group them as 'Others' or leave them as their customer.

To do this I created a calculated dimension and used the following expression

=if(Sum({<[Bill Date]={">=$(=MonthStart(Today(),Day(Today())=1))"} ,

[LinkId] = {"FIN"}
>}
[Bill Value] * $(RevenueRate))=10001, 'Others',[Customer])

Unfortunately this doesn't the expression is Ok but I get a //Error in calculated dimension error is my table when I apply it

Can you please help

Re: Calcualted dimension

Gysbert_Wassenaar — Tue, 16 Aug 2016 10:45:37 GMT

I summary if their invoice total is less than 10001 then group them as 'Others' or leave them as their customer.

[Bill Value] * $(RevenueRate))=10001

If you want less than 10001 then use < instead of =

[Bill Value] * $(RevenueRate))<10001

And the sum should be calculated per customer otherwise you only get the grand total

=if(aggr( Sum({<[Bill Date]={">=$(=MonthStart(Today(),Day(Today())=1))"} , [LinkId] = {"FIN"} >} [Bill Value] * $(RevenueRate)),[Customer])<10001 , 'Others',[Customer])

Re: Calcualted dimension

Tue, 16 Aug 2016 10:59:20 GMT

it was a typo in my expression I still get the same error if I use <

I copied your query with the aggr and the calculated dimension runs but doesn't group it. Do I need t use edit groups button through the dimension tab? I thought a simple if then else statement would work!

Re: Calcualted dimension

Tue, 16 Aug 2016 11:04:11 GMT

my apologies it does but it doesn't include any with a invoice total of 0

Re: Calcualted dimension

Gysbert_Wassenaar — Tue, 16 Aug 2016 11:18:03 GMT

Please post a small qlikview document that demonstrates the problem.

Re: Calcualted dimension

Tue, 16 Aug 2016 11:26:50 GMT

how do I do that

Re: Calcualted dimension

Tue, 16 Aug 2016 11:29:30 GMT

switched the expression round so it picks anything over 10000 as customer and anything less as others.

Thanks for your help

Re: Calcualted dimension

Anonymous — Tue, 16 Aug 2016 11:37:50 GMT

Hi Dave, try this:

=Aggr(if(Sum({<[Bill Date]={">=$(=MonthStart(Today(),Day(Today())=1))"} ,

[LinkId] = {"FIN"}
>}
[Bill Value] * $(RevenueRate))=10001, 'Others',[Customer]) , Customer)

Regards!!

Re: Calcualted dimension

Gysbert_Wassenaar — Tue, 16 Aug 2016 12:24:50 GMT

Open the discussion (don't reply from your inbox), click the Reply button, click the Use advanced editor link to change to the advanced editor. Use the Attach link on the bottom left to attach a file to your post.