Show Worst Sales

[brijesh1991]

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))

Region	MonthYear	Sales	Worst Sales
EIPL	Apr-2018	0.00079554494828958	0.00079554494828958
NJYK	Jan-2018	0.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

[rubenmarin]

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?

[brijesh1991]

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

[rubenmarin]

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%')))

[brijesh1991]

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

[rubenmarin]

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%')))

[brijesh1991]

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

[rubenmarin · Accepted solution]

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

Attached sample without aggr

[brijesh1991]

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