Cross-Table / Variable weird error

[former_member668808]

I've got a cross-table in WebIntelligence that has a variable (a Detail) for the column headings and I've finally managed to cobble together a measure that pulls through the number of instances in the body below each column heading. One of the column headings is blank, and if I filter that out all of the numbers in the body change completely. The figures don't represent the reality at all and I can't figure it out. Images below show before and after the filtering:

The above shows what I see before I filter. The numbers are correct. However, once I apply a filter on the column headings to get rid of the Null/Empty Values, the table is as follows:

Looking at the first row under the column headings, there should be a '2' under Column 5 but it's completely disappeared. I've tried so many workarounds to try and get this to work but it's just not happening. Any advice/guidance from anyone, please?

[ayman_salem]

It depends on which filter you set.

If you set the filter so that the "null" value is not include in the table, this affects the "count" also.

so, in count include the second argument "IncludeEmpty"

integer Count(aggregated_data[;member_set][;IncludeEmpty][;Distinct|All])

If you specify IncludeEmpty as the second argument, the function takes empty (null) values into consideration in the calculation.

also, use "NoFilter" for the count variables

[former_member668808]

Hi Ayman, thanks for responding so quickly, much appreciated.

I don't think I've explained myself too well but have tried your method and got the same result as before. I didn't expect it to work because the values against, for instance, Column5 in the above images aren't null values, they're strings in the database but for some reason, Webi is just treating them as null values. That said, even with that formula, they're still not being picked up - so I've no idea how Webi is viewing them! I just know it's not picking it up as a string.

To clarify further, let's say my dataset is as follows:

Person ID Team Absence Type

137371 Team A Sickness

738273 Team C Other

983391 Team B Sickness

203393 Team A

300494 Team D

500012 Team A Other

881902 Team C Sickness

398279 Team A Sickness

790193 Team D

010938 Team B Other

The cross table should show the following:

Sickness (Blank) Other Total

Team A 2 1 1 4

Team B 1 0 1 2

Team C 1 0 1 2

Team D 0 2 0 2

Total 4 3 3 10

And then when I filter out the (blank) column heading it should show:

Sickness Other Total

Team A 2 1 3

Team B 1 1 2

Team C 1 1 2

Total 4 3 7

But instead I get something like the following:

Sickness Other Total

Team A 0 1 1

Team B 1 0 1

Team C 0 1 1

Total 1 2 3

So it appears to me like it's more to do with how the variable is being read in Webi than necessarily how the cross-table works or how the variable is setup.

[former_member668808]

Hi Ayman,

Not sure when my replies are going to be reviewed and published, but I've figured out the issue (but not yet the solution!). It's where there is more than one instance - if I check the box "Avoid Duplicate Row Aggregation" it removes all instances of 2 or greater in the body. I've now got a choice between two views: 1) checking the avoid duplicate row aggregation box and having most of the values disappear, or 2) unchecking that box and having multiple rows for the same team name, each row representing a different absence.

My Body variable is a simple =Count([Measure];All) formula. How do I get one row pre team, aggregating the absences under the various column headings. It seems so easy yet I've spent the majority of the door trying different solutions with no success. Your help (or the help of anyone else) would be greatly appreciated.

Thanks