How to do 2 levels of aggregation in crosstable

[Rama Kumar]

I'm trying to produce a dynamic table with aninvolved calculation in the following way:

 

There are multiple rows per consumer for multiple products (used as a filter) with time to delinquency - find the minimum time to delinquency for each consumer

Get a cumulative count of time to delinquency based on (1)

 

I'm able to do (1) through a cross table, but not sure how to move from there to 2.

As an example, I'd start off with this table:

 

Initial Data

 

 

Consumer ID

Product

Time to Delinquency

 

 

 

 

0

Credit Card

3

 

 

0

Mortgage

10

 

 

0

Auto Loan

8

 

 

1

Credit Card

6

 

 

1

Student Loan

3

 

 

2

Mortgage

60

 

 

3

Personal Loan

40

 

 

4

Mortgage

50

 

 

4

Credit Card

45

 

 

 

Step (1) would show:

 

 

 

Consumer ID

Min(Time to Delinquency)

 

 

 

 

0

3

 

 

1

3

 

 

2

60

 

 

3

40

 

 

4

 

45

 

 

 

 

Then Step (2)

 

 

 

Min(Time to Delinquency)

Count

Cumulative Count

 

 

 

 

3

2

2

 

 

40

1

3

 

 

45

1

4

 

 

60

1

5

 

 

 

I don't need the intermediate table, just for illustration purposes. Ultimately would like to arrive at the last table, but am not sure how to do so. Would appreciate any help on doing this.

Thanks

[JunYeol Ryu]

The expression of Column axis :

The expression of Row axis :

The expression of value :

count(CASE WHEN [Column3]=Min([Column3]) OVER (Intersect([Axis.Rows])) THEN 1 ELSE NULL END),

count(CASE WHEN [Column3]=Min([Column3]) OVER (Intersect([Axis.Rows])) THEN 1 ELSE NULL END) OVER (AllPrevious([Axis.Rows]))

 

The problem is when you filter something, it doesn't have an effect on Min value

[Rama Kumar]

Hi xuperhero, thanks for your answer. I think the only place where it may breakdown is when Column 3 has duplicates across the different products. I only want one to be counted. Eg:

 

0	Credit Card	3
0	Mortgage	3
0	Auto Loan	3

 

 

 

I'd expect the final output to be:

 

3

1

1

 

 

 

Instead, I see:

 

3

3

3

 

 

 

I resolved this with the following when running on some sample csv data:

 

UniqueCount([iD]) AS count, UniqueCount([iD]) OVER (All([Axis.Rows])) AS cumulative_count

 

One issue I'm facing right now is with the row-expression. I'm working with some in-db data, so am not able to run the OVER expression in there:

 

<Min([Column3]) OVER ([Column1])>

 

Besides importing the data (which is very large btw), do you have any suggestions on how to tackle this

 

Btw, I uploaded a csv file you can use if you like.