Repeat a value in dimension

[#1]

Hi,

I have such problem: I have a fact table in my database which has object_id, type, date and value. I have also two dimensions: Clients and Products which has fields object_id, type, client_id/product_id, client_name/product_name.

In my script it looks like this:Products:

Products:
SELECT object_id
     object_id as product_id,
     name as product_name,
     'P' as type
from Products

Clients:
SELECT object_id
     object_id as client_id,
     name as client_name,
     'C' as type
from Clients

Data:
SELECT Date,
     object_id,
     value,
     'P' as type
from Products_data
where objects_id in (select object_id from Products)
Concatenate:
SELECT Date,
     object_id,
     value,
     'C' as type
from Clients_data
where object_id in (select object_id from Clients)

Then I have chart table:

client_id      sum(value)      sum({<type={"P"}>} value)
K1               210               0
K2               124               0            
K3               200               0
K4               156               0
-                 35              35

Is there any possibility to show 35 instead of zeros? I mean to repeat 'the other' value (in this case 35) on every row with not null client value which doesn't match condition in expression?

Thanks in advance!

[sunny_talwar · Accepted solution]

Try this expression:

=Sum(TOTAL {<type={"P"}>} value)

[#3]

You are great, it works!

[sunny_talwar]

Awesome

I am glad it worked.

Best,

Sunny

[#5]

I have one more question. In this case if I would have also client K5 who would have NULL value (it wouldn't be in Data table), then (when I use TOTAL in expression) it would show up in table. What should I do if I don't want to see these clients (with sum(value)=0, but sum(TOTAL {<type={"P"}>} value)=35)?

[sunny_talwar]

May be this:

If(Sum(Value) = 0, Sum(TOTAL {<type={"P"}>} value))

If(Sum(Value) > 0, Sum(TOTAL {<type={"P"}>} value))

[#7]

It helped to find the finally expression:

If(Sum(Value) != 0, Sum(TOTAL {<type={"P"}>} value)).