topic How to write the between expression ? in QlikView

How to write the between expression ?

suvechha_b — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to write the between expression ?

adamdavi3s — Wed, 01 Mar 2017 09:04:35 GMT

Just write a flexible formula using this logic

Set analysis using date range?

Re: How to write the between expression ?

rahulpawarb — Wed, 01 Mar 2017 09:21:59 GMT

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

Re: How to write the between expression ?

adamdavi3s — Wed, 01 Mar 2017 09:31:21 GMT

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.

Re: How to write the between expression ?

suvechha_b — Wed, 01 Mar 2017 09:53:14 GMT

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.

Re: How to write the between expression ?

rahulpawarb — Wed, 01 Mar 2017 09:59:50 GMT

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

Re: How to write the between expression ?

suvechha_b — Wed, 01 Mar 2017 10:15:43 GMT

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.

Re: How to write the between expression ?

adamdavi3s — Wed, 01 Mar 2017 10:22:40 GMT

Have you tried this? ^

Re: How to write the between expression ?

suvechha_b — Wed, 01 Mar 2017 10:25:31 GMT

Yes , but its not working.

Re: How to write the between expression ?

adamdavi3s — Wed, 01 Mar 2017 10:32:02 GMT

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

Re: How to write the between expression ?

rahulpawarb — Wed, 01 Mar 2017 10:42:57 GMT

Hello Suveccha,

After examining the shared expression, I found that true part of if statement requires changes in Higher Bound and Lower Bound values (Changed condition as - period_key less than equal to vMaxPrev3 and greater than equal to vPeriodKeyPrev).

if( not GetSelectedCount([Fiscal_Year]) or  not GetSelectedCount([Month]),
Sum({<[Fiscal_Year]=,[Month]=,period_key={"<=$(vMaxPrev3)>=$(vPeriodKeyPrev)"},TranType={$(vAmountType)}>}Amount),
Sum({<[Fiscal_Year]=,[Month]=,period_key={"<=$(vSelectMaxPrev3)>=$(vSelectMaxPrev1)"},TranType={$(vAmountType)}>}Amount))

Hope this will be helpful.

Regards!

Rahul