Top 100 Customer who are there in previous Year not in Current Year

[deepakk]

Hi ,

I want to Display Customer who are there in Previous year top 100 and not there in currentb year top 100...

Any one has made such application then please do forward it to me.

Thanks & Regards

Deepak

[#2]

Hi Rikab,

I'he improved my application. I have changed formula in calculated dimension a little bit.

I have also changed the formula in your application. So right now it works fine. Have a look

Milda

[deepakk]

Hi Rikab,

The file size is too high I cant download the file. If you can reduce the data I can check the application.

Regards

Deepak

[#4]

Hi Milda!

Yes! It is working fine. If you don't mind please tell me why it was not working before. You were used IsNull in the calculated dimension. Is that to avoid the null rows? Will be much thankful to you if you can explain the calculated dimension in way even newbie can understand.

Looking forward for your reply!

Thanks and Regards

Rikab

[#5]

Hi Rikab,

The hole expression is quite simple. I sum(values) and then use rank() function to sort the result from biggest to smallest. Then (if I want top 10) I show only these rows where the rank() <=10.

The first version of my formula was not quite good because it worked only with these items, which were in boath years ( if they were and in 2008 and in 2009). But the problem was with new items: the items which appeared only in 2009, and wasn't in 2008. So for this reason i used IsNull() function to test if the item is new.

Hope this will be helpfull and understandable 🙂

Milda

[#6]

Hi Deepak!

I think it is not required now. Milda has provided me the exact solution which i requested for. You can also look into the script and also you need to do the same changes in yours also. Anyway thanks for your help!

Please check if you can help me out here.

http://community.qlik.com/forums/t/25323.aspx

Thanks and Regards,

Rikab

[#7]

Hi Milda and Deepak!

Let me know what changes are required in my document which you have provided the solution for find out last 10. Rest of the criteria is same as the previous one. Only thing is that it has to be filtered according to the last 10.

Please do help me out!

I am also checking from my end also.

Thanks and Regards,

Rikab

[#8]

Hello Deepak and Milda!

Have you found any solution for this? If yes please do let me know!

Regards,

Rikab

[#9]

Hi Deepak and Milda!

Have attached the script if you have any problem in downloading document. I am also working around. Let me know if you have found the solution.

//Top 10 2008
=IF(
aggr(
rank(total
aggr( sum( {$<Year={$(=Max(Year)-1)}>} SalValue),itemname)
,0,1)
,itemname)
<=10,

aggr(
rank(total
aggr( sum( {$<Year={$(=Max(Year)-1)}>} SalValue),itemname)
,0,1)
,
itemname))
//Top 10 2009
=IF(
aggr(
rank(total
aggr( sum( {$<Year={$(=Max(Year))}>} SalValue),itemname)
,0,1)
,itemname)
<=10,

aggr(
rank(total
aggr( sum( {$<Year={$(=Max(Year))}>} SalValue),itemname)
,0,1)
,itemname))
//Top 10 2008 but not in 2009
=IF(
aggr(
rank(total
aggr( sum( {$<Year={$(=Max(Year)-1)}>} SalValue),itemname)
,0,1)
,itemname)
<=10,
 IF(
 aggr(
 rank(total
 aggr( sum( {$<Year={$(=Max(Year))}>} SalValue),itemname)
 ,0,1)
 ,itemname)
 >10,
itemname))
//Top 10 in 2009 but not in 2008
=IF(
 aggr(
 rank(total
 aggr( sum( {$<Year={$(=Max(Year))}>} SalValue),itemname)
 ,0,1)
 ,itemname)
 <=10,
 IF(
 aggr(
 rank(total
 aggr( sum( {$<Year={$(=Max(Year)-1)}>} SalValue),itemname)
 ,0,1)
 ,itemname)
 >10
 OR
 ISnull(aggr(
 rank(total
 aggr( sum( {$<Year={$(=Max(Year)-1)}>} SalValue),itemname)
 ,0,1)
 ,itemname))
 =-1,
itemname))

Thanks and Regards,

Rikab

[#10]

Hi Milda and Deepak!

Got any solution for this? I am working around but did not get any solution. This is very urgent requirement from the customer. Please let me know if you have got any solution.

Regards,

Rikab

[#11]

Hi Rikab,

I've tried to make a table, which shows last items (lowest sales). What i've changed: I have added minus (-) sign in calculated dimension. But I don't know if this will be ok for you.

Go to the "Last top" tab. There I've created 2 similar tables. The only difference is that in the first table expression I've added +1. There is a strange thing: for example, when you select Year 2009, in the second table you will see that "item no" begins from 76. The reason: there are 75 items with 0 sales (in 2008). So in first table when you add +1, you can see all items: from 1 to 75 with 1,00 sales and other items like in second table.

I don't know how to show items with 0 sales. Maybe you will have some ideas.

Hope this will help you.

Milda