Help understanding set analysis with dates and dollar sign expansion

[treborscottnam]

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.

[sunny_talwar · Accepted solution]

Try this

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

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

[treborscottnam]

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!

[sunny_talwar]

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.

[treborscottnam]

That definitely helps my understanding.

Thank you!