topic Dividend with different ganularity levels in QlikView

Dividend with different ganularity levels

Mon, 17 Aug 2015 17:51:13 GMT

Hello there:

I have a Fact table with different leves of granularity, I need the result of [Unit Cost] divided by [Services] As shown in the next image:

I need the total of services to be a constant for all products and families accordig with Embarcacion and YearMonth, so I used the next formula:

=sum (TOTAL <YearMonth ,Embarcacion ,Service> Services)

In the result I get the divisor, but when I select a Family or Product, the Divisor desappear:

So I tried with the next formula:

=sum (TOTAL <YearMonth ,Embarcacion ,Service> {<Family=,Product=>} Services)

So when I select a Family or Product, the divisor Still same as I need, but shows all data as follow:

How can I keep the divisor value and respect the selection of product or family wich have different levels of granularity?

I attach the QV document, any help will be greatful.

Thanks in advance.

swuehl — Mon, 17 Aug 2015 18:02:20 GMT

Maybe like

=sum(aggr(sum ({<Family=,Product=>} Services),YearMonth,Embarcacion,Service))

But you shouldn't see any issues with the existing expression when you use it as denomnator .

edit: Changed to outer sum() aggregation

Tue, 18 Aug 2015 18:00:01 GMT

Thank you for your quik response swuehl, it works, I just make a little adjusments for my own dashboard.