Set analysis null fields

[JohnSamuel123]

hi all,

hoping for some help.

Im trying to do  set analysis on two fields, its a simple count where if one value in a field is null, it gets the other field.

my calculation is: 

=if(isnull(newWork),Count({<oldWork={'Hybrid'}>}[EmpName]), Count({<New={'Hybrid)'}>}[EmpName]))

i have the same calculation but instead of Hybrid it would say "remote", "onsite" etc..

my empname is a unique record. 

however when i start filtering by other fields i dont get the correct values in my set analysis?:

what i should be getting in the above is "2" and not "1" (as i have 2 empname records) in my hybrid set analysis.

any help on how to correctly get the count would be great.

thanks

[a_mullick]

Hi @JohnSamuel123 

If I understand corretly, and you want to show a total count of 'Hybrid' in the left text object whether it's old work or not then this should work:

Count({<oldWork={'Hybrid'}>}[EmpName])+

Count({<New={'Hybrid)'}>}[EmpName])

[JohnSamuel123]

Hi @a_mullick 

thank you for that. 

what i need is for my set anaylsis to check each empname, and if they have a NewWork, use that and add it to the total count, but if NewWork is null, use the iredefaultWork which would never be null. 

i dont just want a count of hybrid in both old and new as this may cause an issue as a user may have "Hybrid" in both old and new, causing a duplicate.

i hope that makes sense,

thanks

[a_mullick · Accepted solution]

Hi @JohnSamuel123 ,

Thanks for clarifying.

I think this should work now.

The first part counts all the new work. It doesn't matter about the NULL() entries here. The second part makes use of the AGGR() function over the EmpName field and flags a 1 if new work is NULL, otherwise 0. Summing this should give the correct 'count' of cases for old work, where new work is NULL().

= Count({<[New] = {'Hybrid'}>} EmpName) +
Sum({<[Oldwork] = {'Hybrid'}>} Aggr(Sum({<[Oldwork] = {'Hybrid'}>} If(IsNull([New]), 1, 0)), EmpName))  

[suepenick]

Nulls are a tricky thing when trying to do value calculations

my solution to keep the data very clean is to load the data and just say 

if(isnull([New Work]), 'x', [New Work]) as NewWork,

in the load script...then 

Load Resident the file again and drop the first table

then there are no NULLS.  you can do what you want and it will work every time.

when there are blanks in the tables for some fields and no blanks for some fields, informationally it works, but not really good for calculations on those null fields.

and if you have huge files, I recommend doing the if(isnull([New Work]), 'x', [New Work]) as NewWork,

then Storing the file as a temp qvd...then dropping the first table and actually loading the stored temp table

the reload works cleanly and goes fast. Calculations are spot on

when Left joining multiple tables to the main data table - you end up with nulls in the fields where there is not data to join to the main....so the loading, storing temporarily... dropping the table...then loading that temp table, then cleaning out the nulls with ' ' or 'x' or '0'  what ever you think is appropriate makes for a very useful file with no nulls to deal with.  you will also notice you won't have any display glitches when they are searching on one of those left joined fields...you cannot select on blanks or nulls, but you can on the replaced values.  this is useful for cleaning up data for engineering purposes...finding related fields missing data.

Hope this helps