Successive count for each month

[jagannalla]

Hi,

I've data like this

MonthYr	Color	AccountId
Apr 2015	Red	1
Apr 2015	Red	2
Apr 2015	Green	3
Apr 2015	Green	4
Apr 2015	Yellow	5
May 2015	Red	6
May 2015	Yellow	7
May 2015	Green	8
Jun 2015	Yellow	9
Jun 2015	Green	10
Jun 2015	Green	11

Now I need o/p:

Apr-15	Red	3
	Green	2
	Yellow	1
15-May	Red	2
	Green	1
	Yellow	1
Jun-15	Red	0
	Green	2
	Yellow	1

- The count of Red is based on start month to end month. from Apr to jun we had 3 reds, from May to Jun we had 2 and in jun we don't have any red's

- I want to use only 2 dimension with 1 exp (Dim are MonthYr,Color and exp as count(AccountId)

- Every thing needs to done in script level only.

[#2]

Hi Jagan,

I think the easiest way to do this is to setup one count for each color and the group the table load on Year-Month.

Good luck!

[jagannalla]

Could you give me one example script

[MayilVahanan · Accepted solution]

Hi Jagan,

Try like this

ColorTemp:

LOAD *, MonthYr&Color as ColorKey INLINE [

    MonthYr, Color, AccountId

    Apr 2015, Red, 1

    Apr 2015, Red, 2

    Apr 2015, Green, 3

    Apr 2015, Green, 4

    Apr 2015, Yellow, 5

    May 2015, Red, 6

    May 2015, Yellow, 7

    May 2015, Green, 8

    Jun 2015, Yellow, 9

    Jun 2015, Green, 10

    Jun 2015, Green, 11

];

//Cross Join

ColorTemp2:

LOAD Distinct MonthYr Resident ColorTemp;

Join LOAD Distinct Color Resident ColorTemp;

//Concatenate the missed color to corresponding month

Concatenate (ColorTemp)

LOAD MonthYr,Color Resident ColorTemp2 Where not Exists(ColorKey, MonthYr&Color) ;

DROP Table ColorTemp2;

DROP Field ColorKey;

//Date format

Color:

LOAD *, Month(Date) as Month, Month(Date)*1 as MonthNum;

LOAD *, Date(Date#(MonthYr, 'MMM YYYY')) as Date Resident ColorTemp;

DROP Table ColorTemp;

//Find the max month

MaxMonthTable:

LOAD Max(Month) as MaxMonth Resident Color;

Let vMaxMonth = Peek('MaxMonth', 0, 'MaxMonthTable');

DROP Table MaxMonthTable;

//Join the month from small to large

Join(Color)

load Distinct

Month as Month_AsOf,

MonthNum + IterNo()-1 as Month

Resident Color

while MonthNum+IterNo()-1 <= $(vMaxMonth);

//only for Red

FinalTable:

LOAD MonthYr, Color, AccountId, Month_AsOf as Month Resident Color Where Color = 'Red';

LOAD Distinct MonthYr, Color, AccountId, Month Resident Color Where Color <> 'Red';

DROP Table Color;

EXIT Script;

Then use Month, Color as Dimension. =Count(AccountId) as Measure.

Hope it helps you.

[#5]

Hi,

Check the attached file

T1:

LOAD * INLINE [

    MonthYr,Color

    Apr 2015, Red

  Apr 2015, Red

  Apr 2015, Green

  Apr 2015, Green

  Apr 2015, Yellow

  Jun 2015, Yellow

  Jun 2015, Green

  Jun 2015, Green

  May 2015, Red

  May 2015, Yellow

  May 2015, Green

];

T2:

LOAD

  MonthYr,

  count(if(Color = 'Red',1)) as Red,

  count(if(Color = 'Yellow',1)) as Yellow,

  count(if(Color = 'Green',1)) as Green

Resident T1

Group By MonthYr;

[Kushal_Chawda]

Please see the attached.

There only one red from May 2015 to Jun 2015 instead of two as you have mentioned.