Calcualted dimension

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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])

[#3]

Hi

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!

[#4]

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

[Gysbert_Wassenaar]

Please post a small qlikview document that demonstrates the problem.

[#6]

how do I do that

[#7]

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

Thanks for your help

[Anonymous]

Hi Dave, try this:

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

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

Regards!!

[Gysbert_Wassenaar]

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.