Skip to Content
author's profile photo Former Member
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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

7 Answers

  • Best Answer
    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


    Add a comment
    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

    Add a comment
    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

    Add a comment
    10|10000 characters needed characters exceeded

  • 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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    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])

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 21, 2014 at 07:29 AM

    Hi Agnes,

    Instead of using:

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

    how about this:

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

    Could you check if this works?

    Regards,

    Srilakshmi B

    Add a comment
    10|10000 characters needed characters exceeded

  • 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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.