# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

## 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?

1 Solution

Accepted Solutions
Contributor III

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

8 Replies
MVP & Luminary

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

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

talk is cheap, supply exceeds demand
MVP

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

Creator III

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

Contributor III

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 ?

Contributor III

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

M

MVP

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.

Contributor III

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.

Contributor III

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

Tags