topic Re: Bucket movement related count in QlikView

Bucket movement related count

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Bucket movement related count

Wed, 11 May 2016 23:59:33 GMT

Got it working now. Thanks.

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

Re: Bucket movement related count

Thu, 12 May 2016 00:04:53 GMT

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)

Re: Bucket movement related count

Thu, 12 May 2016 23:27:30 GMT

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

Re: Bucket movement related count

sunny_talwar — Fri, 13 May 2016 13:51:56 GMT

How about this:

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

Assuming Month is number formatted

Re: Bucket movement related count

Mon, 16 May 2016 00:03:09 GMT

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.