topic Re: Date in Set Analysis? in QlikView

Date in Set Analysis?

qlikofba — Tue, 05 Apr 2016 16:01:59 GMT

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?

Re: Date in Set Analysis?

swuehl — Tue, 05 Apr 2016 17:21:12 GMT

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?

Re: Date in Set Analysis?

qlikofba — Tue, 05 Apr 2016 17:34:27 GMT

Is This what you mean?

Re: Date in Set Analysis?

sunny_talwar — Tue, 05 Apr 2016 17:35:06 GMT

Can you try this:

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

Re: Date in Set Analysis?

swuehl — Tue, 05 Apr 2016 21:32:17 GMT

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