topic Re: Different selections for Numerator and Denominator in App Development

Different selections for Numerator and Denominator

flames — Sat, 16 Nov 2024 21:43:10 GMT

I have two tables Table 1 and Table 2. I need to calculate a ratio.
Explained how i do manually, But need to implment in Qlik sense sheet.
for the denominator, i need to get sum by State, by ignoring CITY.
But in Numerator, it should be Sum of Sales by CITY.

Table 1:

CITY STATE SALES
==============================
DALLAS TX 3000
DALLAS TX 800
DALLAS TX 200
AUSTIN TX 1500
AUSTIN TX 500
AUSTIN TX 1000
HOUSTON TX 1000
SFO CA 6000
SFO CA 3000
LA CA 4000
SanDiego CA 5000

Table 2:

CITY STATE COST
======================================
DALLAS TX 1000
--- TX 1400
DALLAS TX 2300
AUSTIN TX 900
--- TX 400
AUSTIN CA 1200
HOUSTON CA 2300
SFO CA 1500
---- CA 870
---- CA 4000
SanDiego CA 130

Now, in qlik sense, i need to calculate
[SUM of SALES by CITY] /[ SUM OF COST by STATE]

for example :

CITY RATIO
=======================================================================
DALLAS (3000+800+200) / (1000+1400+2300+900+400) = 0.67
AUSTIN (1500+500+1000) / (1000+1400+2300+900+400) = 0.5
HOUSTON (1000) / (1000+1400+2300+900+400) = 0.17
SFO (6000+3000) / (1200+2300+1500+870+4000+130) = 0.9
LA (4000) / (1200+2300+1500+870+4000+130) = 0.4
SanDiego (5000) / (1200+2300+1500+870+4000+130) = 0.5

Can someone help with Qlik sense query for this ?
to make it in a sheet.

Thanks in Advance.

Re: Different selections for Numerator and Denominator

sunny_talwar — Mon, 17 Dec 2018 20:06:14 GMT

Try this

=Sum(SALES)/Aggr(NODISTINCT Sum(COST), STATE)

Re: Different selections for Numerator and Denominator

flames — Mon, 17 Dec 2018 23:30:13 GMT

Thanks for the suggestion. But it's not getting calculated as per the requirement.

Here is the result.

Re: Different selections for Numerator and Denominator

sunny_talwar — Tue, 18 Dec 2018 12:23:55 GMT

Not entirely sure, but this could be because of the incorrect mapping you have in your cost table

I believe that AUSTIN and HOUSTON should be from TX instead of CA, right? This is the script I used

Table1:
LOAD * INLINE [
    CITY, STATE, SALES
    DALLAS, TX, 3000
    DALLAS, TX, 800
    DALLAS, TX, 200
    AUSTIN, TX, 1500
    AUSTIN, TX, 500
    AUSTIN, TX, 1000
    HOUSTON, TX, 1000
    SFO, CA, 6000
    SFO, CA, 3000
    LA, CA, 4000
    SanDiego, CA, 5000
];

Table2:
LOAD * INLINE [
    CITY, STATE, COST
    DALLAS, TX, 1000
    ---, TX, 1400
    DALLAS, TX, 2300
    AUSTIN, TX, 900
    ---, TX, 400
    LA, CA, 1200
    LA, CA, 2300
    SFO, CA, 1500
    ----, CA, 870
    ----, CA, 4000
    SanDiego, CA, 130
];

Re: Different selections for Numerator and Denominator

flames — Tue, 18 Dec 2018 17:52:10 GMT

i changed that, and ran.

but still couldn't get desired result.

did you get the expected result with that query ?

A:
LOAD * INLINE [ CITY,STATE,SALES
DALLAS,TX,3000
DALLAS,TX, 800
DALLAS,TX, 200
AUSTIN,TX,1500
AUSTIN,TX, 500
AUSTIN,TX,1000
HOUSTON,TX,1000,
SFO,CA,6000
SFO,CA,3000
LA,CA,4000
SanDiego,CA, 5000
];

Concatenate (A)
LOAD *
INLINE [ CITY,STATE,COST
DALLAS, TX,1000
-, TX,1400
DALLAS,TX,2300
AUSTIN, TX ,900
-,TX,400
AUSTIN, TX,1200
HOUSTON,TX,2300
SFO,CA,1500
-,CA, 870
-,CA,4000
SanDiego,CA,130];

Re: Different selections for Numerator and Denominator

sunny_talwar — Tue, 18 Dec 2018 18:08:22 GMT

Hahahaha now since you have changed them to TX... the denominator will change, right?

But now we have 1200 for AUSTIN and 2300 for HOUSTON

So, in your initial output image had 6000 for Texas in denominator... but now we have 9500 which will change your output, right? This is what I have