topic Re: Count by specific month in QlikView

Count by specific month

danielnevitt — Mon, 02 Sep 2013 10:28:37 GMT

Hi,

I have a create a table that shows company count by disinct trade numbers; expression - Count(aggr(count(Disinct Company),Trade_Num)):

Company Count

A 136

B 90

C 55

I would like to create additional columns to show the count for each specific month.

Company Count Sep Oct

A 136 100 36

B 90 80 10

C 55 50 5

Ideally I would like the month count to be calculated using two different paramenters:

Sep - Trade_Create_Dt <= 30/09/2013 and Pricing_End_Dt >= 30/09/2013

Oct - Trade_Create_Dt <= 31/10/2013 and Pricing_End_Dt>= 31/10/2013

Is this possible?

Any help will be much appreciated.

Thanks,

Daniel

Re: Count by specific month

Mon, 02 Sep 2013 10:41:16 GMT

Hi,

What are fields in your table can you tell elaborate or

Can you please post your sample data

Thanks and regards

S.Amuthabharathi

Re: Count by specific month

CELAMBARASAN — Mon, 02 Sep 2013 10:56:27 GMT

Hi,

I hope

Count(aggr(count(Disinct Company),Trade_Num))

can be simply written as

Count(Disinct Trade_Num)

for Sep you can write a expression

Count({<Trade_Create_Dt={"<=30/09/2013"} ,Pricing_End_Dt={">=30/09/2013"}>} DISTINCT Trade_Num)

Re: Count by specific month

danielnevitt — Mon, 02 Sep 2013 13:42:47 GMT

Hi,

Thank you for your help.

I have another quick question with regards to counts by maximum pricing dates. Currently I use the following expression Count(aggr(count(distinct Company), HDR_Num)). Is there a way of making the count against the maximum Pricing_End_Dt?

Example:

Company Trade_Num HDR_Num Pricing_End_Dt

A 8081201 8081200 30/09/2013

A 8081202 8081200 31/10/2013

A 8081203 8081200 30/11/2013

Using the above I would like to see:

Pricing_End_Dt Count

30/09/2013 0

31/10/2013 0

30/11/2013 1

Is that possible? Each HDR_Num can have multiple Trade_Num's attached to it.

Thanks,

Daniel

Re: Count by specific month

MEllinghausen — Mon, 02 Sep 2013 15:22:57 GMT

Hi,

you can try if(Pricing_End_Dt=max(total Pricing_End_Dt),count(Pricing_End_Dt),0)

Marcus

Re: Count by specific month

CELAMBARASAN — Mon, 02 Sep 2013 15:28:39 GMT

May be like this

Sum(aggr(if(Pricing_End_Dt=Max(Total Pricing_End_Dt), Count(Distinct Company)), HDR_Num,Pricing_End_Dt))

Re: Re: Count by specific month

danielnevitt — Mon, 02 Sep 2013 17:04:21 GMT

Hi,

Unfortunately I am unable to get the code to work.

I have attached a sample qvw file.

I would really appreciate if you could detail on here how to implement the code.

Thanks,

Daniel