Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts
I am using a Straight table and calculating the difference in Sales
Formula is Difference = [Last Month Sales] - [Current Month Sales]
Difference is sorted in Ascending order, at the top negative values and below are positive values
Now in that Chart I want to show only top 20 Negative values
How Shall I do this by using set analysis
Please Suggest
Use the dimension limits tab to restrict it to show only smallest 20 values. Have you given that a shot?
HI
MY dimensions are Branch & customer
When I Restrict Customer by First 20 positive values are also coming
Please Suggest
You might be able to use rank function to get the results you are looking for... but you will need to share some information related to your expression... Having a sample application would be ideal.
Hi,
Please check the screenshot. Select the field in Presentation Tab and change the Max Number to 20.
Thanks,
Muru
HI
Here with this I attached the Sample
but in the sample its working correctly but not in my actual QVW
my Actual QVW Screen shot is attached
Please Suggest
Hi Murugesh
Thank you
I tried this way but not able to bring the total for those 20
it is generating overall total
Does this looks like what you want??
Used the following calculated dimension:
=If(Aggr(Rank(-(Sum([Last Month SPD]) - Sum([Current Month SPD]))), [Customer Account Number]) <=20, [Customer Account Number])
and suppressed the Null (on the dimension tab)
Also attaching the sample app for reference.
Best,
Sunny
HI Sunny
Thank you
The Screen shot is exactly what I am Looking for
but the Lost Month SPD and Current Month SPD are Calculated in that same table
my Actual Data looks like the attached Xls
My Formula for calculating
Lost Month SPD = Sum({< [DEC_MonthYear] =, DEC_Date = {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -1))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} Sales)
and Current Month SPD = Sum({< DEC_MonthYear=, DEC_Date = {'>=$(=Date(MonthStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))'} >} Sales)
Please Suggest
Can you see if this works:
=If(Aggr(Rank(-((Sum({< [DEC_MonthYear] =, DEC_Date = {'>=$(=Date(MONTHSTART(AddMonths(vMaxSalesDate, -1))))<=$(=Date(MONTHEND(AddMonths(vMaxSalesDate, -1))))'} >} Sales)) - (Sum({< DEC_MonthYear=, DEC_Date = {'>=$(=Date(MonthStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))'} >} Sales)))),
[Customer Account Number]) <=20, [Customer Account Number])
I am not sure if I got all the parenthesis right so make sure those are in order