topic Re: Help understanding set analysis with dates and dollar sign expansion in QlikView

Help understanding set analysis with dates and dollar sign expansion

treborscottnam — Tue, 22 Jun 2021 16:44:42 GMT

Hello,

In QlikView, I am trying to get my head around Set Analysis, dates in variables, dollar sign expansion and how they all should be working together.

I have this expression in a TextBox and I am getting the correct results:

=SUM( {<FiscalYearStart = { ">3/1/2019"} > } Sales )

As I understand it, this sums up all the sales where the FiscalYearStart is after 3/1/2019.

Also I have this variable :
vConvertIntToDate = Date(Date#($1,'YYYYMMDD'),'M/D/YYYY')

So if I use it in a TextBox like this:
=$(vConvertIntToDate(20190301))

I get the proper results of: 3/1/2019

Now when I try to combine them, like this:

=SUM( {< FiscalYearStart = { "> $(vConvertIntToDate(20190301)) "} > } Sales )

I get 0 as a result.

I am hoping for some insight on what I am doing wrong.

Thank you.

Re: Help understanding set analysis with dates and dollar sign expansion

sunny_talwar — Tue, 22 Jun 2021 19:12:49 GMT

Try this

=Sum({<FiscalYearStart = {">$(=$(vConvertIntToDate(20190301)))"}>} Sales)

Basically, you need another dollar sign expansion on top of your variable here...

Re: Help understanding set analysis with dates and dollar sign expansion

treborscottnam — Tue, 22 Jun 2021 19:37:53 GMT

sunny_talwar,

That worked! 😂 I really appreciate your help! I was so stuck on this issue.

I was reading about equal signs, dollar expansion and when variables are evaluated, but I think I am missing something.

My guess of what you fixed is that the first $ expands out the variable before evaluating the entire expression. So my version didn't really expand it and may have had this expression:

=SUM( {< FiscalYearStart = { "> vConvertInToDate(20190301) "} > } Sales )

with the $ and = the expression became this:

=Sum({<FiscalYearStart = {"> =3/1/2019" }>} Sales)

Is that what was going on?

Thank you again for the help/solution!

Re: Help understanding set analysis with dates and dollar sign expansion

sunny_talwar — Tue, 22 Jun 2021 19:45:37 GMT

Ya, basically $(=....) tells it to expand whatever is inside it... so look at this

The expression in yellow is

=Sum({<FiscalYearStart = {">$(=$(vConvertIntToDate(20190301)))"}>} Sales)

The expression in Blue is

=Sum({<FiscalYearStart = {">$(vConvertIntToDate(20190301))"}>} Sales)

So, the variable was replaced with the expression within vConvertIntToDate, but it wasn't calculating the final value to use here.

Re: Help understanding set analysis with dates and dollar sign expansion

treborscottnam — Wed, 23 Jun 2021 17:13:23 GMT

That definitely helps my understanding.

Thank you!