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: 
Anonymous
Not applicable

Selectively sum distinct values...

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

9 Replies
jayanttibhe
Creator III
Creator III

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

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

vinieme12
Champion III
Champion III

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

sum(sales) / salesgoal)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
jonathandienst
Partner - Champion III
Partner - Champion III

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:

     Fact:

     LOAD *,

          'Sale' as Source

     From Sales;

     Concatenate(Fact)

     LOAD *,

          'Goal' as Source

     From SalesGoals;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Hi Jayant,

I thought of this, but the expression aggr(distinct sum([Sales Region KPI]) , intRegionTemp) results in every row equal to "-" (i.e. just a dash and not values) .  What does that mean? I was assuming that it meant that I wrote the equation wrong somehow, but editor does say it is OK. Does QLIK show dashes when it hits a null value? (i.e. 15 + null =  "- ")or is this a way to show that I hit a run time error in my formula?

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

Here is a snip of what some test data looks like, notice how the total for Jan does not equal the sum total of its parts due to the same goals being set to southwest and northeast.

Example.PNG

Anonymous
Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

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