Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Direct Discovery
Oracle DB
How do you get the denominator of the share % calc to ignore Product dimension.
Sales Measure:
Sales | Time | |
Product | May | June |
A | 10 | 30 |
B | 15 | 20 |
C | 13 | 21 |
D | 19 | 32 |
E | 18 | 54 |
Total | 75 | 157 |
Desired output (Denominator for Share Calc):
Sales | Time | |
Product | May | June |
A | 75 | 157 |
B | 75 | 157 |
C | 75 | 157 |
D | 75 | 157 |
E | 75 | 157 |
How do you get this in an expression.
AGGR(SUM(Sales),TIME) is giving the desired number but it displays only under one Product. As in, I get the below output:
Sales | Time | |
Product | May | June |
A | 75 | 157 |
B | - | - |
C | - | - |
D | - | - |
E | - | - |
What is the syntax to ignore Product and get the desired output?
Thanks in advance.
Try this:
Sum(Sales)/Sum(TOTAL <Time> Sales)
or
Sum(Sales)/AGGR(NODISTINCT SUM(Sales),TIME)
UPDATE: Added <Time> after reading through the requirement and seeing the response above me
try
sum(Total <Time> Sales)
Try this:
Sum(Sales)/Sum(TOTAL <Time> Sales)
or
Sum(Sales)/AGGR(NODISTINCT SUM(Sales),TIME)
UPDATE: Added <Time> after reading through the requirement and seeing the response above me
Thanks Sunny,
This worked - AGGR(NODISTINCT SUM(Sales),TIME)
Awesome, I am glad it did.
Would you be able to close the thread by marking correct answer.
Qlik Community Tip: Marking Replies as Correct or Helpful
Best,
Sunny
Hi Sunny,
Just an addendum to this.
What if instead of TIME I would like to reuse this formula (dynamically) for other dimensions example Region, Territory
AGGR(NODISTINCT SUM(Sales),TIME)
AGGR(NODISTINCT SUM(Sales),REGION)
AGGR(NODISTINCT SUM(Sales),TERRITORY)
Is it possible to combine the above three?
In the report either one of them will be used. Either there is TIME or REGION or TERRITORY in the report.
So instead of writing the formula for each dimension can we combine the formulas and make it dynamic?
Its doable, but how do you plan to toggle between TIME, REGION or TERRITORY?
TIME, REGION or TERRITORY will be 3 different reports.
I wanted to reuse the same measure across the 3 reports
3 reports? meaning 3 different charts?
yup