Sum for Null values (in Fields)

[StefanE]

Good day to you, reader

I once again hope someone can give me a hint into the right direction.

So in a QS App I need to create a KPI for working days on the department level. What I got are working days per Team as seen in the example Table 1).

I want to create a new KPI that sums up "Working days" on the Department level like in Table 2). The formula

Sum({$< [Product] = >} Total <[Department]> [KPI_Working Days])

sums up the totals for each department nicely.

Now, the problem occurs when on display I want to exclude the empty fields/Null values for the dimension "Product".  There are only working days for the not empty fields left as shown in Table 3).

What do I need to do to get to Table 4)? 

Adjusting the formula like this...

Sum({$< [Product] = >} Total [KPI_Working Days])

doesn't help. If there's no Department selected, the total of Working days for all Departments are shown. 

I hope it's kind of clear what I need.

Anyhow, thanks in advance for your support and/or suggestions.

Kind regards

Stefan

[kaushiknsolanki]

Hi,

You should try this.

Sum(Aggr(Sum(total <Department>WorkingDays),Department,Product))

Have Department, Team, and Product as Dimension and Above expression as Measure.

[avinashelite]

Try like this, in script create one more column like this

If(len(trim(product)) >0,'Y','N') as Product_filter

Now in set analysis you use this new column to get the products which has value 

Sum({$< Product_filter > } Total [KPI_Working Days])

[StefanE]

Hi Kauhik,

Thanks so much for your reply. Unfortunately this function causes problems, if there is more than one product to look at so it seems. I went another route as will be mentioned later.

Thanks again and kind regards 

Stefan

[StefanE]

Hi Guys,

Thanks to all contributors for your replies. To at least temporarily solve my issue I went another route. 

Since I had two formulas, that worked in different settings, I combined the two:

if(GetSelectedCount([Department]) >0,
Sum({$< [Product] = >} Total  [KPI_Working Days])

Sum({$< [Product] = >} Total <[Department]> [KPI_Working Days]))

This works for now but it won't work if I select a team, so further development is needed.

Thanks and kind regards

Stefan