Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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])
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!
my apologies it does but it doesn't include any with a invoice total of 0
Please post a small qlikview document that demonstrates the problem.
how do I do that
switched the expression round so it picks anything over 10000 as customer and anything less as others.
Thanks for your help
Hi Dave, try this:
=Aggr(if(Sum({<[Bill Date]={">=$(=MonthStart(Today(),Day(Today())=1))"} ,
[LinkId] = {"FIN"}
>}
[Bill Value] * $(RevenueRate))=10001, 'Others',[Customer]) , Customer)
Regards!!
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.