topic Re: Max Sales in QlikView

Max Sales

Mon, 15 Sep 2014 10:18:14 GMT

Hi friends,

I have some sales value interval wise,my requirement is to find max sales value for the selected interval,and if no interval is selected then the text box show max sales value from all interval,anyone please help me out.

I have attached a sample app

regards

bhawna

Re: Max Sales

anbu1984 — Mon, 15 Sep 2014 10:31:14 GMT

Try this expr

='Max Sales'&chr(10)&if(Interval='0-1 month',sum([0-1 month]),if(Interval='2-3 month',sum([2-3 month]),if(Interval='5-6 month',sum([5-6 month]),

(If(sum([0-1 month])>sum([2-3 month]),If(sum([0-1 month])>sum([5-6 month]),sum([0-1 month]),sum([5-6 month])),sum([2-3 month]))

))))

Re: Max Sales

geert_gelade — Mon, 15 Sep 2014 10:34:40 GMT

A possible solution is to change the script to:

Sales:

load * inline [

Project, 0-1 month,2-3 month,5-6 month

P1,10,20,30

P2,80,50,20

P3,10,5,20

P4,60,10,10

];

SalesFinal:

CrossTable(Interval,Sales)

load *

resident Sales

Put list box for Interval and in the text box "='Max Sales = ' & chr(10)&max(Sales)"

Re: Max Sales

Mon, 15 Sep 2014 10:36:43 GMT

Hi,

Is that Datamodel fixed? I mean, everything would be easier like:

* inline [

Project, Fact, Interval
P1,10,0-1 month
P1,20,2-3 month
P1,30,5-6 month
P2,80,0-1 month
P2,50,2-3 month
P2,20,5-6 month
P3,10,0-1 month
P3,5,2-3 month
P3,20,5-6 month
P4,60,0-1 month
P4,10,2-3 month
P4,10,5-6 month
];

Load * inline [
Interval
0-1 month
2-3 month
5-6 month
];

Re: Max Sales

Mon, 15 Sep 2014 10:38:18 GMT

I cant make changes in data model

Re: Max Sales

geert_gelade — Mon, 15 Sep 2014 10:42:41 GMT

That's what I tried to accomplish with the crosstable-load.

Re: Re: Max Sales

tresB — Mon, 15 Sep 2014 10:48:50 GMT

='Max Sales'&chr(10)&

If( Not GetSelectedCount(Interval), RangeMax(Max([0-1 month]),Max([2-3 month]),Max([5-6 month])),

if(Interval='0-1 month',max([0-1 month]),if(Interval='2-3 month',max([2-3 month]),if(Interval='5-6 month',max([5-6 month]))))

)

PFA

Re: Max Sales

Mon, 15 Sep 2014 10:52:38 GMT

Thank you all

Re: Max Sales

Gabriel — Mon, 15 Sep 2014 11:17:54 GMT

Hi Bhawna,

I know your question have been answered, however if you wish to take the Max from Project perspective.

So say you select either P1.....P4 from Project list, what is the Max:-

This expression code answer that question

='Max Range For Project'& Chr(10)&

if(not GetSelectedCount(Project),RangeMax(Max([0-1 month]),Max([2-3 month]),Max([5-6 month])),

if(Project='P1',RangeMax(Max([0-1 month]),Max([2-3 month]),Max([5-6 month])),

if(Project='P2',RangeMax(Max([0-1 month]),Max([2-3 month]),Max([5-6 month])),

if(Project='P3',RangeMax(Max([0-1 month]),Max([2-3 month]),Max([5-6 month])),

IF(Project='P4',RangeMax(Max([0-1 month]),Max([2-3 month]),Max([5-6 month])))))))

Best Regards,

Gabriel

Re: Max Sales

Mon, 15 Sep 2014 11:32:41 GMT

but if I select more than one interval then it wont show the max value for those interval,Could you please explain me this how would I do this,I forgot to mention this in my Question

Re: Re: Max Sales

tresB — Mon, 15 Sep 2014 11:40:43 GMT

Well, here is the one; much more dynamic expression:

=$(='RangeMax('&Concat('Max(['&Interval&'])',',')&')')

Cheer!