Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Let's suppose that I have a measure called Orders.Diff and a dimension Client
I wish to create a bar graph (and a straight table) that meets the following requirements:
a) Top N Clients by sum(Orders.Diff) where N is stored in a variable vTopDiffs
b) sum(Orders.Diff) < vDiffThreshold
The requirement in a) was easily achieved with:
Calculated Dimension: =aggr(if(rank(sum(Orders.Diff)) <=vTopDiffs, Client), Client)
Expression: = sum(Orders.Diff)
Any ideas how to achieve b)
Simply replacing
= sum(Orders.Diff)
with
= if(sum(Orders.Diff) < vDiffThreshold, sum(Orders.Diff))
does not work. I suspect the above needs to be added to the Calculated dimension but I have no idea how to do that. Any ideas?
Thanks in advance
Alexis
Maybe like
=aggr(if(rank( if(sum(Orders.Diff) < vDiffThreshold, sum(Orders.Diff)) ) <=vTopDiffs, Client), Client)
If using QV11, you could also look into dimension limits:
Use as first expression something like
=sum({<Client={"=sum(Orders.Diff)<vDiffThreshold"}>}Orders.Diff)
as dimension just Clients and for this dimension, enable 'Restrict which values ...' with option 'show only' with 'Largest' vTopDiffs Value
on dimension limits tab.
Hope this helps,
Stefan
Maybe like
=aggr(if(rank( if(sum(Orders.Diff) < vDiffThreshold, sum(Orders.Diff)) ) <=vTopDiffs, Client), Client)
If using QV11, you could also look into dimension limits:
Use as first expression something like
=sum({<Client={"=sum(Orders.Diff)<vDiffThreshold"}>}Orders.Diff)
as dimension just Clients and for this dimension, enable 'Restrict which values ...' with option 'show only' with 'Largest' vTopDiffs Value
on dimension limits tab.
Hope this helps,
Stefan
Thanx Stefan,
BOTH suggestions works perfectly so THANKS!
Can you explain to me the set analysis statement:
=sum({<Client={"=sum(Orders.Diff)<vDiffThreshold"}>}Orders.Diff)
Thank you once again
Alexis
It's a set expression with an advanced search in the set modifier (please check also the somewhat short explanations and sample in the Help file).
Search all Clients where sum(Order.Diff)<vDiffThreshold.
You can use a similar search expression e.g. in the search bar of a list box (with field Client) to filter the Clients with low sum(Order.Diff).
Hope this helps,
Stefan