Help with MAX

[#1]

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.

[jvitantonio]

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

[#3]

Pls try this one ....

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

Regards,

Antony.

[#4]

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.

[jvitantonio]

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.

[#6]

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]

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

[#8]

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]

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

Remove the =  from both variables and try again

[CELAMBARASAN]

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