Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

chanchalsharma
New Contributor III

Set Analysis behaving differently with same syntax in qliksense

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.

1 Solution

Accepted Solutions
chanchalsharma
New Contributor III

Re: Set Analysis behaving differently with same syntax in qliksense

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)))

3 Replies

Re: Set Analysis behaving differently with same syntax in qliksense

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')

ssarawgi
New Contributor III

Re: Set Analysis behaving differently with same syntax in qliksense

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
New Contributor III

Re: Set Analysis behaving differently with same syntax in qliksense

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)))

Community Browser