Strange aggregation results with set analyzis and null values

[#1]

I'm, trying to make an several aggregations in a single chart. The aggregated values are from different tables. One of the aggregated values is filtered with set analyzis to count only values that have nulls in parameters fields (CID). So I try to use {$-<CID={'*'}>}. When these aggregations go without additional dimensions, everything is OK, but when with the one the results are unexpected.

I do expect in the chart "With num" the first Count to be 1 for lines with Num<>null and I don't know, where from the first line with Num=null appears. Maybe something wrong is with my set analyzis? Thanks in advance!

[swuehl]

Alexander,

I had also strange issues with set analysis with NULLs in linked tables before, and it had given me headaches trying to understand (and now to remember what the cause was).

If you are interested in the records with NULLs, I would recommend to create either an additional flag field to indicate these records (or modifying the source field directly):

AI:

LOAD ID,

  if(len(trim(CID)),0,1) as isCIDNull,

  CID,

  DocNum,

  DocDate,

  PayDate

FROM

try.xlsx

(ooxml, embedded labels, table is Лист2);

or

AI:

LOAD ID,

if(len(trim(CID)),CID,'NULL') as CID,

  DocNum,

  DocDate,

  PayDate

FROM

try.xlsx

(ooxml, embedded labels, table is Лист2);

Then filtering your data for the count:

Count({<isCIDNull={1}>}PayDate)

Or if you don't want to do this, maybe calculating your count like

=count(PayDate) - count({<CID= {"*"}>}PayDate)

may fit your needs.

See also attached.

[#3]

Thank you. Your "Count-Count" really suits my task, but still I wan't to understand, how to avoid such null counts behavior in the future. Maybe someone can clarify, what's wrong?