topic Help with MAX in QlikView

Help with MAX

Mon, 27 Feb 2012 09:54:15 GMT

Hello,

I have an expression that shows me what the Max sales qty of a product was in a period of time.

max({$<[Barcode Stock] = >}aggr(sum({$<[Barcode Stock] = ,[Rolling Period Stock]={">=$(=vPeriodQuarterStartPast)<=$(=vRollingPeriod)"}>}[Qty Stock]),[Barcode Stock],[Rolling Period Stock]))

this works fine and returns the max qty of sales in the best period.....

what I want is an expression to show me which period the sale was in....

example:

Period	Sum of Qty
1	15
2	10
3	25
4	24
5	20
6	16

so my expression would tell me the max sales qty was 25, and i'm after an expression to tell me the period was 3.

Thanks,

Ben.

Re: Help with MAX

jvitantonio — Mon, 27 Feb 2012 10:42:31 GMT

Hi, here it is. Let me know if you need anything else

Re: Help with MAX

Mon, 27 Feb 2012 10:49:58 GMT

Pls try this one ....

sum({$<[Sum of Qty]={$(=max([Sum of Qty]))}>}Period)

Regards,

Antony.

Help with MAX

Mon, 27 Feb 2012 11:09:35 GMT

Hi thanks for your replies.

have tried both but no luck as of yet.

the Sum of Qty is from this expression

max({$<[Barcode Stock] = >}aggr(sum({$<[Barcode Stock] = ,[Rolling Period Stock]={">=$(=vPeriodQuarterStartPast)<=$(=vRollingPeriod)"}>}[Qty Stock]),[Barcode Stock],[Rolling Period Stock]))

so there are multiple lines that need to be calculated to get the total max sales qty in each period. see below....

Period	Qty
1	5
1	10
2	10
3	5
3	10
3	10
4	24
5	20
6	16

so in Period 3 we had the max sales of 25....

thanks again,

Ben.

Help with MAX

jvitantonio — Mon, 27 Feb 2012 11:12:55 GMT

Ben, did you check my application? Please download it, go to Settings --> Variable Overview, and where it says "a" paste your expression max({$<[Barcode Stock] = >}aggr(sum({$<[Barcode Stock] = ,[Rolling Period Stock]={">=$(=vPeriodQuarterStartPast)<=$(=vRollingPeriod)"}>}[Qty Stock]),[Barcode Stock],[Rolling Period Stock]))

OF course this should be done directly in your application but make sure to follow the logic in my application.

Help with MAX

Mon, 27 Feb 2012 11:24:21 GMT

Hi,

i have done this and it gives me a period but it is only right a very small percentage of the time....

it seems to work when the max sales are in the 1st month in the data..... but if the max is for example in period 10 but i have sales in periods 1, 2, 3, 4, 5, 6...... it shows the period as 1

thanks,

ben.

Help with MAX

jvitantonio — Mon, 27 Feb 2012 11:31:03 GMT

If it doesn't work is because your "max" formula is wrong. Please check your formula or upload your application so we can check it. We can't guess and work with "ghost" fields we don't see

Help with MAX

Mon, 27 Feb 2012 11:40:32 GMT

I think you could be right about my max formula.

I cant upload my app as its to big....

I need an expression to get the MAX sum(Qty) if the periods are between the 2 variables below,

[Rolling Period Stock]={">=$(=vPeriodQuarterStartPast)<=$(=vRollingPeriod)"}

any ideas?

Thanks,

Ben.

Re: Help with MAX

jvitantonio — Mon, 27 Feb 2012 11:47:44 GMT

Why do you put a "=" in front of the variable name? =vPeriodQuarterStartPast

Remove the = from both variables and try again

Help with MAX

CELAMBARASAN — Mon, 27 Feb 2012 11:49:13 GMT

Hi,

Try using this

=FirstSortedValue({$<[Barcode Stock] = >} Period,-aggr(sum({$<[Barcode Stock] = ,[Rolling Period Stock]={">=$(=vPeriodQuarterStartPast)<=$(=vRollingPeriod)"}>}[Qty Stock]),[Barcode Stock],[Rolling Period Stock]))

Hope it helps

Celambarasan

Help with MAX

Mon, 27 Feb 2012 11:59:21 GMT

Thanks,

This works a treat!!

Cheers,

Ben.

Help with MAX

Mon, 27 Feb 2012 12:12:33 GMT

Hi,

Slight issue though, with some of the products they have the same max qty in differentt periods so it is just returning an error...... any way i can add an if(expression = error,return latest max,return normal max)

Thanks,

ben.