Bucket movement related count

[#1]

Hi, I have a requirement where I need to count the number of entities that moved from a particular bucket A to a different bucket C.

The data will look as follows.

Month, Bucket, Acct

1, A,A1

2,C,A1

1,A,A2

2,C,A2

3,A,A1

4,C,A1

5,A,A3

6,A,A3

The requirement is such that you select the bucket movement of your choice. let us say A to C. Now we can see A1 and A2 have moved from A to C during the January-February period. So I select month as Feb and from bucket as A and to bucket as C, I need the result to be "2". This should be charted with Month on the X axis and the respective count of movements against it. I don't want to do any script based solutions for this. I am trying various options but not getting the desired result.

Any help would be great.

Thanks,

Ram

[#2]

Got it working now. Thanks.

=Count( {< Month={2},Bucket={'C'},Acct= P({<Month={1},Bucket={'A'}>} Acct) >} Acct)

[#3]

To fit in a chart I modified like this. P() is a real gem.

=Count( {< Bucket={'C'},Acct= P({<Month={"=Max( total Month)-1"},Bucket={'A'}>} Acct) >} Acct)

[#4]

sorry,Month={"=Max( total Month)-1"} -> this doesn't work at all. Any technique to reference a value of the column Month despite Month being the dimension in the chart?

All I am trying to do here is get the count of the accounts that were in state 'A' the previous month and 'C' in the current month and chart it with 'Month' as the dimension. Of course we can solve this by making script changes, but I am looking for a pure GUI based work around/solution.

Thanks,

Ram

[sunny_talwar]

How about this:

Month={"$(=Max(Month)-1)"}

Assuming Month is number formatted

[#6]

No Sunny. Didn't solve the issue. Please see the attachment. The graph is not returning data for August/April where the movement from A-C is seen. However you can see months Feb and November.