Set Analysis behaving differently with same syntax in qliksense

[chanchalsharma]

I have an expression:

= sum({< CommonDate ={">=$(vCurrentMonthStart) <= $(vCurrentMonthEnd)"}>}ActivationQty)

Which is giving me the desired result on the selected month name, however the below expression is not giving the result instead it gives me 0 as an output.

Need urgent help.

= sum({< CommonDate ={">=$(vPreviousMonthStart) <= $(vPreviousMonthEnd)"}>}ActivationQty)

vCurrentMonthStart =Floor(Monthstart(Max(Activation_date)))

vCurrentMonthEnd =Floor(MonthEnd(Max(Activation_date)))

vPreviousMonthStart =Floor(Monthstart(Addmonths(Max(Activation_date),-1)))

vPreviousMonthEnd =Floor(Monthend(Addmonths(Max(Activation_date),-1)))

CommonDate is converted to number format.

Please help.

[sunny_talwar]

You need to add Date() function to your variables so that it matches the format for CommonDate.

vCurrentMonthStart =Date(Floor(Monthstart(Max(Activation_date))), 'CommonDateFieldFormat')

vCurrentMonthEnd =Date(Floor(MonthEnd(Max(Activation_date))), 'CommonDateFieldFormat')

vPreviousMonthStart =Date(Floor(Monthstart(Addmonths(Max(Activation_date),-1))), 'CommonDateFieldFormat')

vPreviousMonthEnd =Date(Floor(Monthend(Addmonths(Max(Activation_date),-1))), 'CommonDateFieldFormat')

[sandeep_sarawgi]

I think your approach looks right overall.

I would suggest not changing the dates into number.  I created a similar model in Qlik and seem to get the right results.

vCurrentMonthStart =Monthstart(Max(Activation_date))

vCurrentMonthEnd =MonthEnd(Max(Activation_date))

vPreviousMonthStart =Monthstart(Addmonths(Max(Activation_date),-1))

vPreviousMonthEnd =Monthend(Addmonths(Max(Activation_date),-1))

On a separate though related note, while the expression sum({< CommonDate ={">=$(vPreviousMonthStart) <= $(vPreviousMonthEnd)"}>}ActivationQty) does the job, when in doubt you could also try evaluating the three components and checking separately in text boxes (see below).  Also you could create an expression using the three below instead of the more compact expression when in doubt.

=sum(ActivationQty)

=sum({<CommonDate ={"<=$(vPreviousMonthStart )"}>}ActivationQty)

=sum({<CommonDate ={">=$(vPreviousMonthEnd )"}>}ActivationQty)

[chanchalsharma · Accepted solution]

Hi all,

It worked fine with the expressions below.

I created a field Monthname(CommonDate) as Month_Activation

= sum({< CommonDate ={">=$(vPreviousMonthStart) <= $(vPreviousMonthEnd)"},Month_Activation= >}ActivationQty)

= sum({< CommonDate ={">=$(vCurrentMonthStart) <= $(vCurrentMonthEnd)"},Month_Activation= >}ActivationQty)

Variables:

vCurrentMonthStart =Floor(Monthstart(Max(Activation_date)))

vCurrentMonthEnd =Floor(MonthEnd(Max(Activation_date)))

vPreviousMonthStart =Floor(Monthstart(Addmonths(Max(Activation_date),-1)))

vPreviousMonthEnd =Floor(Monthend(Addmonths(Max(Activation_date),-1)))