Get total from fact by ignoring a specific dimension

[JSDEV]

Given that "Fact Sale" does not have "Combo" dimension but "Fact Combo" does, how can I get the total units_sold from it (see screenshot below)?

I've tried to ignore "Combo" dimension by using the "Total" keyword but it doesn't return anything:

sum({<fact_type={'Fact Sale'}>} total <category,product> units_sold)

[devarasu07]

Hi,

Try like this,

sum(Total {<category=,product=,fact_type={'Fact Sale'}>} units_sold)

[sunny_talwar]

This

Sum({<fact_type={'Fact Sale'}>} TOTAL units_sold)

[JSDEV]

@sunny_talwar that's close.

I've updated the screenshot to include two products.

How could I get the subtotal for each product from "Fact Sale" (see blue and green highlights) instead of the overall total?

[JSDEV]

unfortunately the user will need to be able to filter by Product and Category so they can't be ignore from the set analysis.

[sunny_talwar · Accepted solution]

in that case, this should have worked

sum({<fact_type={'Fact Sale'}>} total <category,product> units_sold)

or may be you need by just category or product?

sum({<fact_type={'Fact Sale'}>} total <category> units_sold)

or

sum({<fact_type={'Fact Sale'}>} total <product> units_sold)

[JSDEV]

thanks @sunny_talwar, we were wrecking our head trying to understand why it wasn't working as we had definitely done it in the past.

Just test it on QS June 2019 release and it worked as expected. We'll upgrade our Prod environment which is  currently on QS Nov 2017.

Thanks again,

[sunny_talwar]

You can try this in the old version

Aggr(NODISTINCT 
    Sum({<fact_type = {'Fact Sale'}>} units_sold)
, category, product)