8 Replies Latest reply: Dec 9, 2016 11:53 PM by Michael Cox

Help with how to use set P() or E() modifier with variable

Dear Commnity

I have the following set selection formula:

Sum({\$ <[Period]= {'=\$(=\$(vCP))'}  >} [Hours])

It correctly applies a selection to include data where Period = vCP, where vCP is a variable containing the current period.

What I have been unable to achieve is the following: I want to

a) disregard any selections on Period made by the user (ie is should only show Period = vCP) and

b) apply any other selections on other fields (eg city, group etc).

I think this is achieved through P() or E() but I cannot fathom how to incorporate with the use of the variable.

Any suggestions?

• Re: Help with how to use set P() or E() modifier with variable

a) Sum({1<[Period]= {'=\$(=\$(vCP))'}  >} [Hours])

b)  Perhaps Sum({1<City=P(City), Group=P(Group), [Period]= {'=\$(=\$(vCP))'}  >} [Hours])

• Re: Help with how to use set P() or E() modifier with variable

The expression in it current state should already meet your second requirement

Sum({\$<[Period] = {'=\$(=\$(vCP))'}>} [Hours])

or if you just want to honor City and Group selection, this might be give you a slightly different result from Gysbert's expression

Sum({1<City=\$::City, Group=\$::Group, [Period]= {'=\$(=\$(vCP))'}>} [Hours])

• Re: Help with how to use set P() or E() modifier with variable

Thanks @SunnT and gwassenaar

The use of p() and \$:: both did the trick, thank you! - but now selection for Period is not returning the data for vCP it is returning all data. I thought it was but realise it wasn't right.

If I test it with

Sum({1 <[Period]= {'31/10/2016'}  >} [Chargeable Hours])

then I definitely get the right results (just October) and I've also tested that vCP = 31/10/2016.

Any suggestions ?

• Re: Help with how to use set P() or E() modifier with variable

Is vCP an expression? such as Max(Period) or something along those lines? If it is an expression, then you might need to ignore selections in your variable's expressions the same way you are ignoring them in your main expression.

• Re: Help with how to use set P() or E() modifier with variable

Yes, vCP is max(ActualDates) where ActualDates is a table of dates. vCP effectively the latest of these dates. The table of ActualDates is not linked or relates to any other tables.

• Re: Help with how to use set P() or E() modifier with variable

You have to add all the fields you do not want to apply, without value behind it, so try this one, this is what you are looking for :

Sum({\$ <[Period]= {'=\$(=\$(vCP))'}, Year, Month, Qarter  >} [Hours])

• Re: Help with how to use set P() or E() modifier with variable

Sorry Thomas - didn't work. It is still picking up all the data and not limiting it to where Period = vCP

M

• Re: Help with how to use set P() or E() modifier with variable

Ok, I managed to fix my own problem.  For reference here it is:

Sum({1<OUCity=P(OUCity), Group=P(Group),[Period]={"\$(=\$(vCP))"}>} [Chargeable Hours])

This post (here) was a great help and the trick was setting the format on the vCP variable using Date ( ...., 'DD/MM/YYY')