Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
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
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')

View solution in original post

8 Replies
MVP & Luminary
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

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
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
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
Contributor III

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

M

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

View solution in original post