topic Re: Retrieve the Date of First Sales in Pivot Table in QlikView

Retrieve the Date of First Sales in Pivot Table

Tue, 15 Jul 2014 08:24:24 GMT

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

Re: Retrieve the Date of First Sales in Pivot Table

its_anandrjs — Tue, 15 Jul 2014 08:35:22 GMT

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.

Re: Retrieve the Date of First Sales in Pivot Table

Tue, 15 Jul 2014 08:37:15 GMT

Thanks

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

Re: Retrieve the Date of First Sales in Pivot Table

its_anandrjs — Tue, 15 Jul 2014 09:01:16 GMT

Then write a dimension like

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

Expression:- Count(ACCOUNT)

Re: Retrieve the Date of First Sales in Pivot Table

Tue, 15 Jul 2014 09:04:15 GMT

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

Re: Retrieve the Date of First Sales in Pivot Table

its_anandrjs — Tue, 15 Jul 2014 09:12:27 GMT

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

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