Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Partner
Partner

Show Worst Sales

It seems simple, but I am missing something here. I have a straight table with Region, MonthYear  as dimensions and I need to show the Sales  and Worst Sales (Minimum).  Also when selecting Month or Year, there should not be any impact (Nullification of Month and Year).


Below is my expression for

Sales:

(sum({$<Month=,Year=>} GREAT_120+D6+D7+D8+D9+[>D10])/sum({$<Month=,Year=>} LENS_COUNT_COMP))


Worst Sales:

if(Rank(num(Year)+num(Month)/12+10000000*Aggr(-sum(GREAT_120+D6+D7+D8+D9+[>D10])/sum(LENS_COUNT_COMP),Region,MonthYear))=1,

sum({$<Month=,Year=>} GREAT_120+D6+D7+D8+D9+[>D10])/sum({$<Month=,Year=>} LENS_COUNT_COMP))

RegionMonthYear SalesWorst Sales
EIPLApr-20180.000795544948289580.00079554494828958
NJYKJan-20180.0056818181818182

I am able to achieve Sales and Worst Sales, However problem is nullification of Month and Year is not working meaning when I select any month or year, this chart is also filtered based on selected month or year. however that should not happen as per my requirement.

Also if Sales is zero for two Regions then it should show latest month's 0 sales as Worst sales.

I would aopreciate any possible ideas.

Regards,

Brijesh

Tags (4)
1 Solution

Accepted Solutions

Re: Show Worst Sales

Yes, seems related to aggr(), why it's needed?

Attached sample without aggr

8 Replies

Re: Show Worst Sales

Hi Brijesh, maybe?

if(Rank(num(Only({$<Month=,Year=>} Year))+num(Only({$<Month=,Year=>} Month))/12+10000000*Aggr(-sum({$<Month=,Year=>} GREAT_120+D6+D7+D8+D9+[>D10])/sum({$<Month=,Year=>} LENS_COUNT_COMP),Region,MonthYear))=1,

sum({$<Month=,Year=>} GREAT_120+D6+D7+D8+D9+[>D10])/sum({$<Month=,Year=>} LENS_COUNT_COMP))

If doesn't works, can you upload a sample to check?

Partner
Partner

Re: Show Worst Sales

Hi Ruben Marin,

Appreciated your response on this. "Only" is not helping to solve the problem.

I have attached sample QVW and sample data in attachment.

Data is quite simple; Region, MonthYear are as dimensions and we need to show Sales % and Worst Sales %(Minimum Sales%) as Expressions; Month or Year selection should not impact this object. (If there are similar Minimum Sales % for two Regions then latest month need to be shown)

Currently Sales % is working fine.However Worst Sales % is not working as expected. I have used rank to achieve this, but when minimum % is similar for more than one region or minimum % is zero then its not working as expected.

I have attached QVW and Sample data in excel with required description.

Any possible idea will be warmly welcomed. Thank you!

Regards,

Brijesh

Re: Show Worst Sales

Hi Brijesh, in this sample the value is hidden because in presentation tab the option to 'supress zero values' is checked, so the 0% is deleted.

Unchecking the bos will show the 0% (worst percentage) and also all the null values, to shoe only the 0% you can leave the option checked and use this expression to avoid the interpretation of zero as a number:

if(Rank(num(Year)+num(Month)/12+10000000*Aggr(-sum(GREAT_120+D6+D7+D8+D9+[>D10])/sum(LENS_COUNT_COMP),Region,MonthYear),4)=1,

text(Num(sum({$<Month=,Year=>} GREAT_120+D6+D7+D8+D9+[>D10])/sum({$<Month=,Year=>} LENS_COUNT_COMP), '0%')))

Partner
Partner

Re: Show Worst Sales

Hi Ruben,

Thank you for this. I appreciate your solution, I tried implementing your solution in my original file and I think you solved my 99% problem. Miinimum % is showing as expected.

However I am wondering even though we are having nullification of Month and Year in Worst month % expression, by selecting any month or year, it's filtering the object as well and minimum % is moving to selected month or year. For example: If for a XYZ region, Apr2018 is having minimum %. Now if I select Jun, then % will move to June2018 (which is not having a minimum %)

Regards,

Brijesh

Re: Show Worst Sales

And adding the other sets?

if(Rank(num(Only({$<Month=,Year=>}Year))+num(Only({$<Month=,Year=>}Month))/12+10000000*Aggr(-sum({$<Month=,Year=>}GREAT_120+D6+D7+D8+D9+[>D10])/sum({$<Month=,Year=>}LENS_COUNT_COMP),Region,MonthYear),4)=1,

text(Num(sum({$<Month=,Year=>} GREAT_120+D6+D7+D8+D9+[>D10])/sum({$<Month=,Year=>} LENS_COUNT_COMP), '0%')))

Partner
Partner

Re: Show Worst Sales

Hi Ruben, Thank you for this. Unfortunately still same issue (by selecting any month, minimum % is moving to selected month. Is it due to Aggr on MonthYear? Regards, Brijesh

Re: Show Worst Sales

Yes, seems related to aggr(), why it's needed?

Attached sample without aggr

Partner
Partner

Re: Show Worst Sales

Hi Ruben Thank you very much; you are the star!! Your solution is absolute solution what I was looking for. Much appreciated. Have a nice day! Regards, Brijesh