Using Dynamic Values in E()

[sinanozdemir]

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

[sunny_talwar · Accepted solution]

Try this:

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

[sinanozdemir]

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.

[sunny_talwar]

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

[sinanozdemir]

Thanks again sunindia‌.

[sunny_talwar]

No problem. I am glad I was able to help