topic Calculating Difference in QlikView

Calculating Difference

Wed, 17 Jun 2015 13:23:32 GMT

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

Re: Calculating Difference

sunny_talwar — Wed, 17 Jun 2015 13:27:17 GMT

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

Re: Calculating Difference

Wed, 17 Jun 2015 13:34:00 GMT

MY dimensions are Branch & customer

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

Please Suggest

Re: Calculating Difference

sunny_talwar — Wed, 17 Jun 2015 13:41:04 GMT

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.

Re: Calculating Difference

Wed, 17 Jun 2015 14:08:01 GMT

Hi,

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

Thanks,

Muru

Re: Calculating Difference

Wed, 17 Jun 2015 14:23:16 GMT

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

Re: Calculating Difference

Wed, 17 Jun 2015 14:24:48 GMT

Hi Murugesh

Thank you

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

it is generating overall total

Re: Calculating Difference

sunny_talwar — Wed, 17 Jun 2015 14:33:58 GMT

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

Re: Calculating Difference

Wed, 17 Jun 2015 14:53:44 GMT

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

Re: Calculating Difference

sunny_talwar — Wed, 17 Jun 2015 14:58:27 GMT

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

Re: Calculating Difference

Wed, 17 Jun 2015 15:16:58 GMT

HI Sunny

Unfortunately its not Working, its not bringing up any Values

Actually I am Using this expression in the dimension tab to bring up top 200 customer by sales is there

a way to do this around like Ranking in the script itself or using sum, max (sales)

Please Suggest

Re: Calculating Difference

sunny_talwar — Wed, 17 Jun 2015 16:55:33 GMT

I am getting a little confused, can you help me understand what your requirement is? and can you also give a representative data where I can implement it which would help you implement in your final version.

Re: Calculating Difference

Wed, 17 Jun 2015 17:09:32 GMT

HI Sunny

Here Is the Actual Data for one Branch, but I got 152 branches

I want to choose top 200 customers by Sales for each branch for my analysis

for that I am using this expression =aggr(if(rank(sum(SALES))<=200,CUSTOMER),BRANCH,CUSTOMER)

IN Dimensions instead of CUSTOMER to bring top 200 customers by sales for each branch

in expression I am calculating the Last Month SPD ,Current Month SPD and Difference

now my table will show 200 values but I want to show only top 20 lines in the table