 Former Member

# Webi Count based on runningsum

Hi experts,

Need your help in resolving this scenario.

Following is my transaction data

Type Product Month Outstanding Amount X A Jan 10 X A Feb 10 X A Mar -10 X B Feb 30 X B Mar -30 Y C Feb 50 Y C Mar -50

Firstly, I need to get "outstanding amount as at" each period for each product.

Thus I am using formula: runningsum([amount];([product])) to get my result. It works fine.

Product Jan Feb Mar Apr A 10 20 10 10 B 0 30 0 0 C 0 50 0 0

For Product A, outstanding amount as at Feb is 20

For Product A, outstanding amount as at Mar is 10 (10+(-10))

For Product A, outstanding amount as at April is remains 10 (due to no transaction happens)

and so on...

My problem is how do I get count those product having "outstanding amount as at" greater than 0?

Count by Type Jan Feb Mar Apr X 1 2 1 1 Y 0 1 0 0

For eg: Type X,

As at Feb, there are two products (product A,B) are having outstanding amount greater than 0.

As at Mar, only one product (product A) having outstanding amount greater than 0

For eg: Type Y,

As at Feb, one product (product C) having outstanding amount

As at Mar and Apr, no product having any outstanding amount

I tried to use count([product]) where (runningsum([amount];([product])))>0, but couldn't give me a correct result.

Anyone has solution on this?

Many many thanks.

10|10000 characters needed characters exceeded

### Related questions

Posted on Jul 21, 2014 at 10:22 AM

Hi Agnes,

Try below,Find the below structure.. Apply break on Type

In footer:=Count([Product]) Where(Sum(RunningSum([Amount];([Product])))>0)

Select Outline, collapse it to show , footer only

Hide Product dimension 10|10000 characters needed characters exceeded
• Posted on Jul 18, 2014 at 03:45 PM

You could also try:

=Sum(Count([Type]) ForEach([Product]) Where(RunningSum([Outstanding_Amount];([Type];[Product]))>0))

HTH

NMG

10|10000 characters needed characters exceeded
• Former Member

Hi NMG,

Above formula still not working.

For example; Type X: In April, there is no physical transaction happens for any product. It is just the running sum of product A. It returns 0 but the correct count is actually 1.

• Posted on Jul 18, 2014 at 11:48 AM

Hi,

what is the database you are using.

Thanks,

G Sampath Kumar

10|10000 characters needed characters exceeded
• Former Member sampath guntha

Hi Sampath Guntha,

Thanks for your input. Your trick is amazing. But I have too many records, and my WEBi is running out of memory in order to display it.

😔

• Posted on Jul 18, 2014 at 02:11 PM

Hello Agnes,

first create a formula count as below

`=Count([OA];All) Where([OA] >0)-Count([OA];All) Where([OA] <0)`

here OA is outstanding amount.

next create another formula count2 as below

```=RunningSum([count];([type]))
```

use this count 2 it will work.

Regards,

Sree

10|10000 characters needed characters exceeded
• Former Member sree ranganath
Type Product Month Outstanding Amount X A Jan 10 X A Feb 10 X A Mar -10 X B Feb 30 X B Mar -20 Y C Feb 50 Y C Mar -50

Hi Sree,

If you using above data, your formula won't be worked for Mar count.

• Former Member
Posted on Jul 21, 2014 at 02:17 AM

Try this-

=If (Sum([Outstanding Amount]) In ([Type];[Month])=0) Then "" Else Count([Product]) In ([Type];[Month])

10|10000 characters needed characters exceeded
• Former Member
Posted on Jul 21, 2014 at 07:29 AM

Hi Agnes,

count([product]) where (runningsum([amount];([product])))>0

count([type]) where (runningsum([amount];([type])))>0

Could you check if this works?

Regards,

Srilakshmi B

10|10000 characters needed characters exceeded
• Former Member Former Member

FOr this case, then will using Previous ([v_oa]) help instead of Previous ([OA]) in the formula for variable v_oa in my previous post?

• Posted on Jul 21, 2014 at 11:13 AM

Hi Agnes

Try this formula.

=Sum(RunningSum([Outstanding_Amount];([Type];[Product])) ForAll([Product])>0)

Regards

Mustafa