Counting Blank rows for each Funds center

[Former Member]

In WebI I need to count the GL accounts which are blank. For example I have shown a table below, if Funds Center A has blank GL accounts it should be counted if other funds center occuring means the count should be restarted

Funds Center	Count
A
	1
	2
	3
B
	1
	2
C
	1
	2

[CdnConnection · Accepted solution]

Gokulkrishnan,

       You can't do this in WebI.  It is NOT possible to count NO DATA.   The best way to handle this is to update the UNV/UNV to update the SQL statement.  Create a CASE / IF statement to display all BLANKS as a UNIQUE char so that it can be counted within WebI.

Regards,

Ajay

[former_member189638]

Try creating a variable with the following formula and see if this resolves the issue

=If(IsNull([G/L Accounts])) Then RunningCount(Count(1);([Fund Center]))

But I believe you need Fund Center and your measure as well in the report which you can hide by making the text white.

[Former Member]

Hi,

If you want only the count of empty cells (Null cells), you can achieve using the below formula:

=If(IsNull([Quantity sold])) Then(RunningCount([Quantity sold];IncludeEmpty)-RunningCount([Quantity sold]))

Here, First RunningCount(;IncludeEmpty) function counts including Empty.

        Second RunningCount() function counts other than empty.

Hence, the subtraction gives you the Count of Null cells (Empty rows).

PFB Screenshot:

Hope it helps you!!!

Kindly update if you need further help.

Thanks,

Parthiban

[Former Member]

Hi,

The example could have been more helpful if Account Number would have been included as well.

Currently, for specific fund name, you have counts and one blank cell. So, I will have to consider it as a string rather than a number value.

If it would have been a number, the value would have been '0' and not blank/NULL.

If you sort in ascending order, the blank value will come at the end.

Considering this, I have created below formula:

=If(IsNull([GL Acc])) Then RunningCount([GL Acc];([Fund Name])) +1 Else RunningCount([GL Acc];([Fund Name]))

This gives following results:

Hope it will help.

Regards,

Yuvraj

[Former Member]

Thanks for your effort but I want to count only the empty cells..

[Former Member]

Hi,

You can use RunningCount() function as below:

=RunningCount([State];([Year]))

PFB Screenshot:

If you want to calculate the Empty Space, then include the keyword IncludeEmpty before the Reset Dimension. PFB:

=RunningCount([State];IncludeEmpty;([Year]))

In your case, Formula looks like:

=RunningCount([G/L Account];([Funds Center]))

Here,

[G/L Account] is your Dimension(or Hierarchy) object for G/L Account

[Funds Center] is your Dimension object for Funds Center.

As i mentioned, if you want to count empty spaces also, then use the below formula:

=RunningCount([G/L Account];IncludeEmpty;([Funds Center]))

Hope this answer helps you!!!

Thanks,

Parthiban

[Former Member]

Hello Gokulkrishnan,

I don't think it is possible to reset the counter, as it is not possible to identify that the blank value corresponds to a particular funds center.

Thanks,

Madhumitha