Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

p function

Hi

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?

Thanks

p_function.PNG

Message was edited by: Hemang Dave

Message was edited by: Hemang Dave

8 Replies
swuehl
MVP
MVP

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
Author

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

sunny_talwar

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

swuehl
MVP
MVP

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
Author

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.

swuehl
MVP
MVP

What about

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

         product_type = {'EQ'},

         history_rating_salesman_id = p(rating_salesman_id),

         effective_date={"$(=date(max(effective_date),'M/DD/YYYY'))"} 

    >}

product_sales_weight)

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

cheers,

Oleg Troyansky

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

Not applicable
Author

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.

target_account_idproduct_id_effective_date2target_sales_weighttarget_account_name
206091886_2016-02-240.01%asia
2060932722_2016-02-240.03%asia
2060938732_2016-02-240.08%asia
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'},

                 effective_date={'$(=effective_date)'},

                 history_rating_salesman_id={'$(=rating_salesman_id)'},

                 target_rank={'1'},

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

                                                                          effective_date={'$(=effective_date)'}

                                                                  >}

                                                                product_id_effective_date)

          >}

target_sales_weight)

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

Thanks