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 -50Firstly, 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 0For 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 0For 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.
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
You could also try:
=Sum(Count([Type]) ForEach([Product]) Where(RunningSum([Outstanding_Amount];([Type];[Product]))>0))
HTH
NMG
Hi,
what is the database you are using.
Thanks,
G Sampath Kumar
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
Hi Agnes
Try this formula.
=Sum(RunningSum([Outstanding_Amount];([Type];[Product])) ForAll([Product])>0)
Regards
Mustafa
Add a comment