8 Replies Latest reply: Feb 29, 2016 5:39 PM by Hemang Dave RSS

    p function

    Hemang Dave

      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

        • Re: p function
          Stefan Wühl

          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?

            • Re: p function
              Hemang Dave

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

                  • Re: p function
                    Stefan Wühl

                    I think the syntax is correct.

                     

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

                      • Re: p function
                        Hemang Dave

                        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
                            Stefan Wühl

                            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)

                              • Re: p function
                                Hemang Dave

                                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

                              • Re: p function
                                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!