How to write the between expression ?

[suvechha_b]

Hi All ,

How to get the sum of sales amount for previous 3 months ?

It needs to be flexible , if fiscal year , fiscal period is selected , then the sales should be calulated on the selected value. else max period of sales.

sample model attached.

[adamdavi3s]

Just write a flexible formula using this logic

Set analysis using date range?

[rahulpawarb]

Hello Suvechha,

Please refer below given sample expression:

Sum({<Fiscal_Year=, Month=, DateKey = {">= $(=MonthStart(Max(DateKey),-3) <= $(=MonthEnd(Max(DateKey),-1)"}, TranType={$(vAmountType)}>}Amount)

In above expression, DateKey field is the Numeric version of Date field i.e. Floor(Date) AS DateKey in script.

Hope this will be helpful.

Regards!

Rahul

[adamdavi3s]

Actually I think your expression is fine, just use

Sum({<period_key={"<=$(vSelectMaxPrev3)>=$(vSelectMaxPrev1)"},TranType={$(vAmountType)}>}Amount)

The reason I say this is because if you make a selection in year or period then its going to automatically restrict this formula as your variables will reduce based on selections.

[suvechha_b]

Its working , but if I select only Fiscal Year , it is not working.

If I select Fiscal Year in the filter , the model is having the max period key . So the expression should work according.

[rahulpawarb]

Hello Suvechha,

Thank you for the prompt verification. Could you please elaborate more on this? If possible, Also share the updated application file,

Regards!

Rahul

[suvechha_b]

The expression is working  when I select the [Fiscal_Year] and [Month] both or I didn't select anything in the filter.

But , it is not working when I only select [Fiscal_Year].

if( not GetSelectedCount([Fiscal_Year]) or  not GetSelectedCount([Month]),

Sum({<period_key={"<=$(vPeriodKeyPrev)>=$(vMaxPrev3)"},TranType={$(vAmountType)}>}Amount),

Sum({<[Fiscal_Year]=,[Month]=,period_key={"<=$(vSelectMaxPrev3)>=$(vSelectMaxPrev1)"},TranType={$(vAmountType)}>}Amount))

Sample model attached.

[adamdavi3s]

Have you tried this? ^

[suvechha_b]

Yes , but its not working.

[adamdavi3s]

Ok, why?

It seems to work OK for me, but in the shared application there is only one year and month so it is hard to tell