Discussion board where members can learn more about Qlik Sense App Development and Usage.
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.....
Solved! Go to Solution.
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]))
maybe : aggr(sum(distinct sales goal) , MonthID, Region)
Not sure if I understood it clear. Sample QVW will be very helful.
then don't use Sum(Goals) when the relation is many to one; just use
sum(sales) / salesgoal)
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;
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?
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.
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.
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".
If the simple concatenation approach did not work, then you have two choices
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.
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]))