Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Help with MAX

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:

PeriodSum of Qty

1

15
210
325
424
520
616

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.

11 Replies
jvitantonio
Valued Contributor III

Re: Help with MAX

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

Not applicable

Re: Help with MAX

Pls try this one ....

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

Regards,

Antony.

Not applicable

Help with MAX

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

PeriodQty
15

1

10
210
35
310
310
424
520
616

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

thanks again,

Ben.

jvitantonio
Valued Contributor III

Help with MAX

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.

Not applicable

Help with MAX

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.

jvitantonio
Valued Contributor III

Help with MAX

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

Not applicable

Help with MAX

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.

jvitantonio
Valued Contributor III

Re: Help with MAX

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

Remove the =  from both variables and try again

Help with MAX

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

Community Browser