9 Replies Latest reply: Feb 16, 2017 7:03 PM by Christopher Obert RSS

    Selectively sum distinct values...

    Christopher Obert

      Hi everyone, thank you in advance for any thoughts on my latest problem. 

      Let me give some background...

      I need to conduct an analysis that for a KPI. The KPI isi based on a sales goal. Goals are set at a sales region level. The fact table is at a sales transaction level. In the end the KPI sums sales by region and then divides that into the sales goal.


      According to QLIK best practices the best method to add the sales goal to the analysis is to add it to the fact table.


      The problem is that the sales goal does not exist at a transaction level, so when I do a sum(sales) / sum(salesgoal) I am n-counting the sales goal (where n = number of sales made)


      in other words...assume that

      1)  I have a sales goal for the North of $500,

      2) I have 5 sales of $10  in the North.


      The resulting above formula equals "$50 / $2500" (5x10 / 5x500).

      If I use sum(sales) / sum (distinct salesgoal) then it becomes $50 / $500. which at first blush seems great...but here lies the problem.


      Assume I have a another sales goal for the South which is also $500, and one more sale of $5 in the south


      When I group the KPI by a level larger then region the distinct still applies.

      My resulting formula of sum(sales) / sum (distinct salesgoal) equals 55/500 when I put the KPI in a table in a sheet. Where it SHOULD be $55/$1000.

      How do I tell my set analysis to sum(distinct sales goal) only/always  across MonthID and  Region. (where MonthID {201501, 201502,...201701}.

      Thanks for any input that you may have.....

        • Re: Selectively sum distinct values...
          Jayant Tibhe

          maybe : aggr(sum(distinct sales goal) , MonthID, Region)


          Not sure if I understood it clear. Sample QVW will be very helful.

          • Re: Selectively sum distinct values...
            Vineeth Pujari

            then don't use Sum(Goals) when the relation is many to one; just use


            sum(sales) / salesgoal)

              • Re: Selectively sum distinct values...
                Christopher Obert

                Hi Vineeth, thank for the suggestion. 


                This does not work because If I want to look at the KPI by date for example (i.e. total sales goal for Jan 2015) It will not provide a value, it will only show sales goals at the sales region level. I.e Jan 2015 - West = $500 and Jan 2015 - East = $500. But the rollup to  Jan 2015 = " - ".  I need Jan 2015 = $1000.

              • Re: Selectively sum distinct values...
                Jonathan Dienst

                If you load the goals at the correct granularity, then you wont have this problem. You can mix the granularity of the fact table. Concatenate the goals rather than joining them. I would also add a source field that identifies whthere the record is a sale or a sales goal. Here is an outline of the load strategy:



                     LOAD *,

                          'Sale' as Source

                     From Sales;



                     LOAD *,

                          'Goal' as Source

                     From SalesGoals;

                  • Re: Selectively sum distinct values...
                    Christopher Obert

                    Hi Jonathan,

                         Thanks for the suggestion. I am told that this was our original solution/design, but that it did not work. I am sorry, but I was not part of the dev effort for this project at that time, so I do not know what those problems were and no one else seems to remember what the issues were exactly either. We were using some QLIK consultants back then. The first one came up with your solution apparently, and when it did not work we got some additional help from QLIK. 3 more "QLIK experts" actually, and I am told all three firmly stated that the "correct" method is to append the data as a column on the fact. or at least that is  "how you do it in QLIK".

                      • Re: Selectively sum distinct values...
                        Jonathan Dienst

                        If the simple concatenation approach did not work, then you have two choices


                        • understand why it does not work in your case
                        • try an alternative which is going to get increasingly complicated as time goes on


                        If the concatenation approach does not work, then something else is going on over and above what you have described in your post. I cannot even begin to make suggestions as to a possible solution ("does not work" is not a useful problem description). If you are working with a large data set (millions of rows), then I strongly suggest that you find out why the first approach is not working for you, rather than taking the "easy way" out, which may not turn out to be easy in the long run.

                    • Re: Selectively sum distinct values...
                      Christopher Obert

                      Hi all, so the solution was in the aggr() function. This creates a hidden table in the back at the grain defined within the function.  Basically, you need to create a table of the proper grain before summing any values.


                      sum(aggr(distinct max([Sales Goal]), [MonthYear],[Region ID]))