Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Re: Selectively sum distinct values...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Anonymous

Not applicable

2017-02-09
07:56 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

3,432 Views

1 Solution

Accepted Solutions

Anonymous

Not applicable

2017-02-16
07:03 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

9 Replies

jayanttibhe

Creator III

2017-02-09
08:06 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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

vinieme12

Champion III

2017-02-10
01:18 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

If a post helps to resolve your issue, please accept it as a Solution.

3,078 Views

jonathandienst

Partner - Champion III

2017-02-10
01:38 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

3,078 Views

Anonymous

Not applicable

2017-02-10
03:28 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

3,078 Views

Anonymous

Not applicable

2017-02-10
03:33 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

3,078 Views

Anonymous

Not applicable

2017-02-10
03:44 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

3,078 Views

Anonymous

Not applicable

2017-02-10
03:51 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

3,078 Views

jonathandienst

Partner - Champion III

2017-02-12
11:56 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

3,078 Views

Anonymous

Not applicable

2017-02-16
07:03 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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