Count by specific month

[danielnevitt]

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

[#2]

Hi,

What are fields in your table can you tell elaborate or 

Can you please  post your sample data 

Thanks and regards

S.Amuthabharathi

[CELAMBARASAN]

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)

[danielnevitt]

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

[MEllinghausen]

Hi,

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

Marcus

[CELAMBARASAN]

May be like this

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

[danielnevitt]

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