Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

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 Smile

Milda

deepakk
Valued Contributor III

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

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

Not applicable

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

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

Not applicable

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

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 Smiley Happy

Milda

Not applicable

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

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

Not applicable

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

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

Not applicable

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

Hello Deepak and Milda!

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

Regards,

Rikab

Not applicable

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

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

Highlighted
Not applicable

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

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

Not applicable

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

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

Community Browser