Syntax - Share % Calc using Direct Discovery with Oracle DB

[#1]

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.

[lironbaram]

try

sum(Total <Time> Sales)

[sunny_talwar · Accepted solution]

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

[#4]

Thanks Sunny,

This worked - AGGR(NODISTINCT SUM(Sales),TIME)

[sunny_talwar]

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

[#6]

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?

[sunny_talwar]

Its doable, but how do you plan to toggle between TIME, REGION or TERRITORY?

[#8]

TIME, REGION or TERRITORY will be 3 different reports.

I wanted to reuse the same measure across the 3 reports

[sunny_talwar]

3 reports? meaning 3 different charts?

[#10]

yup