topic Re: How to use variable in set expression in QlikView

How to use variable in set expression

daveatkins — Wed, 04 May 2016 14:37:48 GMT

I want to calculate a count from the most recent week of data. I am able to accomplish this by hard coding the following expression:

count({$ <ReportingWeekStart={'4/24/2016'} >} Account)

I define a variable

vSelectionWeekStart = date(WeekStart(max(ReportingDate)))

and display the variable in a text box to make sure it is working...so it displays as "4/24/2016"

however, this expression below returns a count of the entire current selection, not just the most recent week:

count({$ <ReportingWeekStart={"=$(=vSelectionWeekStart)"} >} Account)

What is wrong?

Re: How to use variable in set expression

settu_periasamy — Wed, 04 May 2016 16:36:00 GMT

Maybe try

count({$ <ReportingWeekStart={'$(vSelectionWeekStart)'} >} Account)

count({$ <ReportingWeekStart={'$(=vSelectionWeekStart)'} >} Account)

Re: How to use variable in set expression

sunny_talwar — Wed, 04 May 2016 16:37:14 GMT

Try this:

Count({$<ReportingWeekStart={'$(=vSelectionWeekStart)'}>} Account)

Count({$<ReportingWeekStart={'$(=$(vSelectionWeekStart))'}>} Account)

Re: How to use variable in set expression

daveatkins — Wed, 04 May 2016 16:44:39 GMT

it appears the second one works:

Count({$<ReportingWeekStart={'$(=$(vSelectionWeekStart))'}>}

the first does not. now, if only I understood why? I have a few others like this on the same report...will see how it goes.

Re: How to use variable in set expression

sunny_talwar — Wed, 04 May 2016 16:55:15 GMT

I guess what you need is to replace 4/24/2016 with an expression. To do that first step is to get the variable to expand to a correct number in a text box object. In your case, I am guessing that if you put $(vSelectionWeekStart) in a text box object you will see 04/24/2016. I just moved that within the dollar sign expansion -> $(=Whatever works in text box)