Pivot Table header expand

[bsbernabe]

Hello There,

What my concern is how to use pivot table base on report i try to use it but I'm failed, i need to count all Agreement ID base on Trans_Date and this is what i did:  =num(count({$<[Agreement ID]= {'*'}, Tran_Date=>} [Agreement ID] )) i got ( 94 )which is correct then i need to count (DPD)= {91} base on  MonthYear and  [Agreement ID] that was connected at  Agreement ID into sales sheet count all Agreement ID in as of date that match in Agreement ID at sales sheet i have attached file for my sample data source.

The goal is to display the Trans_Date and total count of Agreement Id in Sales Sheet and expand as of date in header below is the output of what i need.

	MonthYear	May,2015	Jun,2019
Tran_Date	Total Count
Jan,2015	94	20	17

also in as of sheet counting the Agreement ID happen just once, sample if this Agreement Id =133414 already count in a month of May,2015 and also this Agreement ID is existing in month of Jun,2015 no need to count.

Thank you for your help,

Best regards,

Bing

[Arthur_Fong]

Can you attach a sample qvw?

[bsbernabe]

Hello Arthur,

Attached file is my sample qvw.

Best Regards,

Bing

[Arthur_Fong · Accepted solution]

Modified some of your scripts and front end expressions:

Refer qvw attached as reference.

Thanks and regards,

Arthur Fong

[bsbernabe]

Thank you so much very helpful, great support highly appreciated.

Note: additional question  if ever i want to Count (DPD) > {'100'})[Agreement ID]) how can i do that?

Best Regards,

Bing

[Arthur_Fong]

count(if ( DPD>100, [Agreement ID]))