Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating Difference

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

12 Replies
sunny_talwar

Use the dimension limits tab to restrict it to show only smallest 20 values. Have you given that a shot?

Not applicable
Author

HI

MY dimensions are Branch & customer

When I Restrict Customer by First 20 positive values are also coming

Please Suggest

sunny_talwar

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.

Not applicable
Author

Hi,

Please check the screenshot. Select the field in Presentation Tab and change the Max Number to 20.

Thanks,

Muru

First20NegativeValues.JPG

Not applicable
Author

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

Capture.JPG

Not applicable
Author

Hi Murugesh

Thank you

I tried this way but not able to bring the total for those 20

it is generating overall total

sunny_talwar

Does this looks like what you want??

Capture.PNG

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

Not applicable
Author

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

sunny_talwar

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