Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

p function


I've following expression in the set analysis. For some reason it is not interpreting the p function because it is showing as black font and not a bold blue font (the color for functions)

I've highlighted it with a red underline in the picture below.

Any idea what's wrong here?



Message was edited by: Hemang Dave

Message was edited by: Hemang Dave

Tags (1)
8 Replies

Re: p function

I wouldn't bother about the coloring.

Is the syntax checker complaining about your expression?

Or don't you get the implicite field value assignment you want to achieve?

Not applicable

Re: p function

By syntax checker if you mean the OK sign at the end of the expression editor, yes it is not complaining. I am not getting the desired output and the obvious i could think of was the coloring of the code.

edit expression.PNG

Re: p function

I guess what is your requirement? What are you getting and what are you looking to get? May be put it in words and provide a few lines of data to show the input and output


Re: p function

I think the syntax is correct.

I remember a similar discussion with p() function not working as requested, was this issue solved back then?

Not applicable

Re: p function

yes it was. In fact it is working fine in the original app which is a huge file so when i recreated the same problem with a smaller set of data the code is not working.

Regardless, following is the description of what I am trying to achieve.

Boris Backer is a salesman who is assigned to sell a certain products.

Products are sold to clients who hold accounts.

There are also target accounts. Target accounts hold products.

The performance of a salesman is measured by the sales he generates for a given account compared to that account's target sales.

Boris has a certain sales target for each account.

Ultimately I am trying to create a table where column 1 represents accounts, column 2 represents Sales% and column 3 represents Target %.

The data model has some additional fields which you'll see in the attached application file.

On the UI, if a user selects Boris Baker and an effective date (say 02/24/2016).

The table should show all the accounts Boris has sold a product to.

Further the table should also show accounts to which Boris may not have generated any sales as long as one of the products that Boris sells is part of the target account.

Please refer to the attached qvf (Sales vs Target) for further details of the data model and the expression for each columns.


Re: p function

What about

Sum({1<  account_type={'large'},

         product_type = {'EQ'},

         history_rating_salesman_id = p(rating_salesman_id),





Re: p function

One obvious problem that I can see in your Set Analysis is the date formatting.

Your Date field is formatted as M/D/YYYY, while your date filter in Set Analysis has the date formatted as YYYY-MM-DD. This is not going to work. In order to compare dates, they have to be formatted the same way. Removing the date filter from the first expression leads to getting some (not sure if accurate) results, as opposed to nothing.

There might be other issues too, but this one is the most obvious.


Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy and Austin, TX!

Not applicable

Re: p function

Thanks Stefan

That was very useful.

Here is another issue though, The table should show account IGV also because while Boris's sales to IGV account may be zero it's target account ID 20609 (which has target rank 1) has 3 products product id 1886, 32722 and 38732 which Boris sells.

sum of target_sales_weight0.13%

So the table should also show another row that reads as follows

Account                  Sales%                  Target Sales%

IGV                           0%                           0.13%

Ultimately the output should look like the following

sales analysis table.PNG

Instead, currently it is not showing the row for IGV as shown below.

sales analysis table_2.PNG

I've attached the latest qvf file which has following set analysis for Target Sales % column

Sum({1<  account_type={'large'},




                 product_id_effective_date2=p({ 1< history_rating_salesman_id={'$(=rating_salesman_id)'},






Any thoughts why the row for IGV is missing in the table?


Community Browser