11 Replies Latest reply: Feb 27, 2012 7:12 AM by benjmoff

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

• ###### Re: Help with MAX

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

• ###### Re: Help with MAX

Pls try this one ....

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

Regards,

Antony.

• ###### Help with MAX

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.

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

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

• ###### 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

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

• ###### 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

• ###### Help with MAX

Thanks,

This works a treat!!

Cheers,

Ben.

• ###### Help with MAX

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.