table with some null values and show the total for them.

[#1]

Hello

I have 1 master table.

OPR_TYPE, OPR_NAME, RG_NAME, TOT_DUR

I have a config table that shows which are valid configrations.

OPR_TYPE,OPR_NAME, RG_NAME, VALID

i joined them to create 1 table

OPR_TYPE, OPR_NAME, RG_NAME, VALID, TOT_DUR

A,X,1,1,100

A,Y,2,-,200

B,LL,1,1,100

B,MM,1,-,300

- is where there is no entry for it in the config table, so the final table after join has that value as NULL.

if i create a pivot table that shows SUM({$<VALID ={"1"}> TOT_DUR) works fine.

but if i want to show totals of rows that do not have a config attached is not working:

i have the expression as SUM({$<VALID -={"1"}> TOT_DUR)

the table is always shown as null values. not with the ones that have actual totals but no VALID flag.

i have also tried with VALID ={} , VALID ={"$(=Null())"}  but to no avail.

is there something very basic i am missing?

[tresesco · Accepted solution]

Is this what you want? PFA.

[#3]

Thank you..

this is what I want.

but why isnt the set analysis working?

since this is a test, i have very simple fields.

actually i would like to include date ranges to minimize data in the table.

you solution works, but could you explain how to add date ranges / other conditions to filter more data ?

[tresesco]

Hi,

Actually set with '*' does not work because '*' does not include null(). Therefore if you want to use set analysis you can try like:

SUM({$-<VALID ={*}>} DUR)

This should work.

[#5]

Thank you.

That helps.