Announcements
Introducing Conversational Analytics in Microsoft Teams: READ MORE
cancel
Showing results for
Did you mean:
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
MVP

Try this

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

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

4 Replies
MVP

Try this

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

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

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!

MVP

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.

Contributor III
Author

That definitely helps my understanding.

Thank you!

Community Browser