topic Re: Successive count for each month in QlikView

Successive count for each month

jagannalla — Mon, 22 Jun 2015 10:23:29 GMT

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.

Re: Successive count for each month

Mon, 22 Jun 2015 10:34:07 GMT

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!

Re: Successive count for each month

jagannalla — Mon, 22 Jun 2015 10:56:38 GMT

Could you give me one example script

Re: Successive count for each month

MayilVahanan — Mon, 22 Jun 2015 11:26:36 GMT

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.

Re: Successive count for each month

Mon, 22 Jun 2015 11:30:24 GMT

Hi,

Check the attached file

T1:

LOAD * INLINE [

MonthYr,Color

Apr 2015, Red

Apr 2015, Green

Apr 2015, Yellow

Jun 2015, Yellow

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;

Re: Successive count for each month

Kushal_Chawda — Mon, 22 Jun 2015 11:55:05 GMT

Please see the attached.

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