Dynamic Quarter Change

[Fiorrie]

Hi Guys

Can you please advise, I want to replace below formula in red by dynamic statement, but struggle:

This is working

Count(Distinct{<[Buyer]={"=AGGR(If(Sum({<[Qtr Flag]={'0'}>}[# Quantity])>0 AND Sum({<[Qtr Flag]={'1','2','3','4'}>} [# Quantity])=0,1,0),[Buyer])=1"}>} [Buyer])

Now I want to replace Qtr Flag static value with dynamic formula but is working only without aggr/if, here not working:

Count(Distinct{<[Buyer]={"=AGGR(If(Sum({<[Qtr Flag]= {"$(=Max([Quarter ID]))"}>}[# Quantity])>0 AND Sum({<[Qtr Flag]={">= $(=Max([Quarter ID])-4) <= $(=Max([Quarter ID])-1)"}>} [# Quantity])=0,1,0),[Buyer])=1"}>} [Buyer])

Working like this

Count({1<[Quarter ID] = {"$(=Max([Quarter ID]))"}>} distinct [Buyer])

Many thanks

[Stoyan_Terziev]

Hello Fiorrie,

I hope I understand your question right, but let me know if this doesn't answer it.

Most likely you want to check the Quarter ID values that you are setting. Especially if you have consequent years in your model.

The quarter value for Q1 2019  is '1' which is < than the quarter value for Q3 2018 which is '3'.

You can create a numeric field which should look like 201901 /201902 / 201903 / 201904.

Then you can do simple Min/Max in the Set expression from that field. And if you want to set a condition like 'Give me the count of buyers for the last quarter where we had any buyers at all' then you can do this:

Count({1<[Quarter ID] = {"$(=Max({<Buyer={'*'}>} [Quarter ID]))"}>} distinct [Buyer])

I hope this helps!

Kind regards,

S.T.

[Fiorrie]

Hi Stoyan

The quarter ID is not an issue, it nicely provides 4 quarters and actual quarter as per statement. What is not working as soon as I put the max in question marks inside the aggr/if condition, so it is probably missing additional question marks? As the statement is working nicely once is in separate formula, but when I combine them it is not working anymore.

Thanks