Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
treborscottnam
Contributor III
Contributor III

Help understanding set analysis with dates and dollar sign expansion

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.

1 Solution

Accepted Solutions
sunny_talwar

Try this

 

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

 

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

View solution in original post

4 Replies
sunny_talwar

Try this

 

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

 

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

treborscottnam
Contributor III
Contributor III
Author

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

sunny_talwar_0-1624391059594.png

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

That definitely helps my understanding.

Thank you!