8 Replies Latest reply: Aug 31, 2017 3:16 AM by Sam Grounds RSS

    Questions - Set analysis on KPI

    jacob so

      Hi,

       

      I'm quite new to Qlik and would really appreciate some help here.

       

      I'm trying to create one KPI which would not be affected by other filters. I know it should be done by set analysis but it doesn't really work out, here is what I've done so far:

       

      =Sum({<STORE_TYPE={'Main Store'},SALES_DATE={$(vToday)},Store_No={*}>}DAILY_TOTAL_NET_SALES)

      /Sum({<SALES_DATE={$(vToday)}>}OVERALL_SALES_TARGET)-1

       

      Some background, this KPI calculated the actual sales vs target and I need to limit it to all stores in main stores and sales date to be 'vtoday'. I have multiple filters on different store so I don't want people clicking different store would affect the total picture of this KPI.

       

      Hope it's clear and really appreciate your help.

       

      Regards,

      Jacob

        • Re: Questions - Set analysis on KPI
          arul settu

          may be try this

           

          =Sum({<STORE_TYPE={'Main Store'},SALES_DATE={$(vToday)},Store_No=>}DAILY_TOTAL_NET_SALES)

          /Sum({<SALES_DATE={$(vToday)}>}OVERALL_SALES_TARGET)-1

            • Re: Questions - Set analysis on KPI
              jacob so

              Thanks Arul, I did try your expression but it doesn't work... the part is not working is the store_no, as I did try different store type or sales date and it doesn't change, but it's still changing when I choose different stores or region... any other suggestions?

            • Re: Questions - Set analysis on KPI
              Sam Grounds

              I expect it's because your date variable holds a date as a string eg. 30/08/2017, which would need to be put in set analysis as SALES_DATE={'30/08/2017'} (needs single quotes).

               

              How does this work for you?

               

              =(Sum({<STORE_TYPE={'Main Store'},SALES_DATE={'$(vToday)'},Store_No=>}DAILY_TOTAL_NET_SALES)

              /Sum({<SALES_DATE={'$(vToday)'},Store_No=>}OVERALL_SALES_TARGET))-1

               

              Thanks,

              Sam

                • Re: Questions - Set analysis on KPI
                  jacob so

                  The date is correct, I did try to remove the store_no part and it does not change if I change day, the problem now is when I change different store (or different regions with multiple stores), the KPI changes as well..

                   

                  any other suggestion?

                    • Re: Questions - Set analysis on KPI
                      Sam Grounds

                      Is the [Store_No] field the one you are making selections on? If you have a store name field that is being used, you will have to set it to ignore selections on any fields that you are using by adding [StoreName]= into the set analysis of both sums.

                        • Re: Questions - Set analysis on KPI
                          jacob so

                          Tried this and it's still not working (select store and KPI still change)

                           

                          =Sum({<STORE_TYPE={'Main Store'},SALES_DATE={$(vToday)},[US_SITE_NO]=>}DAILY_TOTAL_NET_SALES)

                          /Sum({<SALES_DATE={$(vToday)},[US_SITE_NO]=>}OVERALL_SALES_TARGET)-1

                           

                          or I need to set all the filters which related to store selection (i.e. district, region etc?)

                          • Re: Questions - Set analysis on KPI
                            jacob so

                            I got it now, so i need to set it with all the related store field and now it's working, thanks so much for your help!

                             

                            Regards,

                            Jacob

                              • Re: Questions - Set analysis on KPI
                                Sam Grounds

                                No problem. Glad you managed to figure it out. Yes you will need to specifically reference any field you want to ignore. If you want to ignore ALL fields, then you can simply put a 1 in there instead of the dollar. LIke so,

                                 

                                sum({1}Sales)

                                 

                                This will show ALL sales, none of your selections will count towards this expression. It will sum every record in the Sales field (subject to what you can see after taking into account set analysis). Or if you want to specify a small number of fields to include their selections you can do this like so...

                                 

                                sum({1<MonthField=$::MonthField,YearField=$::YearField>}Sales)

                                 

                                It's like saying "ignore all selections except MonthField and YearField".

                                 

                                Sam