Date in Set Analysis?

[qlikofba]

Hello,

I have an expression

Sum({$<prod_dt={"$(=vLastMonth)"},renewal_dt={">='$(=vMonthStart)' <=$(=vMonthEnd)"}>}curr_bal_amt)

For some reason it is not working.  Originally I tested by doing this.

=Sum({$<prod_dt={'2/29/2016'},renewal_dt={">=3/1/2016<=3/31/2016"}>}curr_bal_amt)

Then I created the vLast month variable, which is =PrevMonthEnd,

then I created vMonthEnd which is MonthEnd(prod_dt)

and also vMonthStart which is MonthStart(prod_dt)

The furthest the expressions got with the variables was

Sum({$<prod_dt={"$(=vLastMonth)"},renewal_dt={">='$(=vMonthStart)' <=3/31/2016"}>}curr_bal_amt)

I would think I would be able to switch 3/31/2016 with '$(=vMonthEnd)' which should work but it is not.

How can I resolve this?

[swuehl]

If you put your expression with the variables in a straight table, leaving the expression label empty, then hover with the mouse over the expression header, what do you see when the variable gets expanded?

[qlikofba]

Is This what you mean?

[sunny_talwar · Accepted solution]

Can you try this:

=Sum({$<prod_dt={"$(=vLastMonth)"}, renewal_dt={"$(='>=' & $(=vMonthStart) & '<=' & $(=vMonthEnd))"}>}curr_bal_amt)

[swuehl]

You already got an answer, but yes, that's what I was talking about.

You probably noticed that your dollar sign expansions created a function call statement, but you need literal values in the numeric search, just like the dollar sign expansion worked for vLastMonth.

So the only thing you need to take care of would have been to correct your variable definitions e.g. vMonthStart should be defined as

=MonthStart(prod_dt)

with a leading equal sign.

Same for vMonthEnd.

Then the field modifier should be as simple as

{< renewal_dt={">=$(vMonthStart)<=$(vMonthEnd)"} >}

The Little Equals Sign

The Magic of Dollar Expansions‌

Dates in Set Analysis