Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
sydtriman
New 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
sydtriman
New Contributor III

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

8 Replies
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand

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

thomaslg_wq
Contributor III

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


sydtriman
New Contributor III

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 ?

sydtriman
New Contributor III

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

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.

sydtriman
New Contributor III

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.

sydtriman
New Contributor III

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

Community Browser