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

[Anonymous]

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?

[Gysbert_Wassenaar]

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

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

[sunny_talwar]

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])

[thomaslg_wq]

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])

[Anonymous]

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 ?

[Anonymous]

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

M

[sunny_talwar]

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.

[Anonymous]

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.

[Anonymous · Accepted solution]

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')