Different selections for Numerator and Denominator

[flames]

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.

[sunny_talwar]

Try this

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

[flames]

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

Here is the result.

[sunny_talwar]

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

[flames]

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

[sunny_talwar]

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