p function

[#1]

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

Message was edited by: Hemang Dave

Message was edited by: Hemang Dave

[swuehl]

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?

[#3]

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.

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

I think the syntax is correct.

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

[#6]

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]

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]

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!

[#9]

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_id	product_id_effective_date2	target_sales_weight	target_account_name
20609	1886_2016-02-24	0.01%	asia
20609	32722_2016-02-24	0.03%	asia
20609	38732_2016-02-24	0.08%	asia
	sum of target_sales_weight	0.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

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

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