topic Re: Using Dynamic Values in E() in QlikView

Using Dynamic Values in E()

sinanozdemir — Wed, 09 Dec 2015 21:20:42 GMT

Hi all,

I am not quite sure if I am doing something wrong, but here is the scenario. I would like to sum amounts for companies that didn't exist in a given time period. The below is the sample dataset. As you can see D and C never had activities in 2015-01.

As of now, I am using the below expression to show the companies that didn't exist in 2015-01:

Sum({1<Company=E({1<Date={'2015-01'}>})>}Amount)

What I would like to achieve is to make 2015-01 dynamic. So far, I used GetFieldSelections(Date) and it is not working:

Sum({<Company=E({<Date={"$(=GetFieldSelections([Date]))"}>})>}Amount)

I am not able to attach the qvw right now, but if someone has a suggestion it would be great.

Thanks

Re: Using Dynamic Values in E()

sunny_talwar — Wed, 09 Dec 2015 21:28:27 GMT

Try this:

Sum({1<Company=E({1<Date={$(=Chr(39) & GetFieldSelections([Date], Chr(39) & ',' & Chr(39)) & Chr(39))}>})>}Amount)

Re: Using Dynamic Values in E()

sinanozdemir — Wed, 09 Dec 2015 21:42:36 GMT

As always amazing.

Thank you so much sunindia‌. One question though, why do we have to go through this nonsense just to get the field selection? Would you mind breaking down chr(39) parts?

Thank you again.

Re: Using Dynamic Values in E()

sunny_talwar — Wed, 09 Dec 2015 21:47:31 GMT

Sure, so when you were doing this hard-coded way you would have done something like this for two dates, right?

Sum({1<Company=E({1<Date={'2015-01', '2015-02'}>})>}Amount)

The part I colored red is the exact thing you will see when you put -> =Chr(39) & GetFieldSelections([Date], Chr(39) & ',' & Chr(39)) & Chr(39) in a text box. So essentially I am replicating the hard-coded numbers using GetFieldSelections() function in a text box. Once I have that I just placed it between $() in the set modifier.

I hope that helps.

Best,

Sunny

Re: Using Dynamic Values in E()

sinanozdemir — Wed, 09 Dec 2015 21:49:00 GMT

Thanks again sunindia‌.

Re: Using Dynamic Values in E()

sunny_talwar — Wed, 09 Dec 2015 21:50:32 GMT

No problem. I am glad I was able to help