Retrieve the Date of First Sales in Pivot Table

[#1]

I have a pivot table with account as a dimension. Another dimension is the Date (Year-Month). For each account, there are several dates available, corresponding to dates with revenues for this account. Ex.

ACCOUNT      Year-Month

Acc1              2011-07

Acc1               2012-06

Acc1               2012-07

Acc2               2009-07

Acc2               2010-06

and I would like to get the following dimensions computed: Year of first revenue, and Flag

ACCOUNT      Year-Month          Flag          Year of First Revenue

Acc1              2011-07               New           2011

Acc1               2012-06               Existing     2011

Acc1               2012-07               Existing      2011

Acc2               2009-07               New            2009

Acc2               2010-06               Existing     2010

I struggle for having the flag set to New for the first date for each account, and else to existing.

Which approach would you suggest?

For instance, is it possible to get the list of values for one dimension for each value of another dimension: ex. for Acc1, get the list [2011-07,2012-06,2012-07] ?

Thanks in advance

[its_anandrjs]

For a list try like

Ex:-

T1:

LOAD * INLINE [

    ACCOUNT, Year-Month

    Acc1, 2011-07

    Acc1, 2012-06

    Acc1, 2012-07

    Acc2, 2009-07

    Acc2, 2010-06

];

T2:

LOAD

ACCOUNT, Concat([Year-Month],',') as List

Resident T1 Group By ACCOUNT;

And plot List field

And can you elaborate for Flag and  Year of First Revenue i am not understand.

[#3]

Thanks

I cannot use the script, I am using calculated dimensions only

[its_anandrjs]

Then write a dimension like

Dim:- =Aggr( Concat([Year-Month],','),ACCOUNT)

Expression:- Count(ACCOUNT)

[#5]

Could you explain ? what is Concat([Year-Month],',') for ? Is your answer giving the year of first sales?

[its_anandrjs · Accepted solution]

No it is for List like [2011-07,2012-06,2012-07]

And what is mean for first year of sale let me know.