How to get pivot table based on a condition on a set analysis calculation that is available in a straight table

[vvira1316]

Hi,

I've following straight table where Aging - Request Date is calculated as per Expression provided below. I need to create a Pivot table as seen in second screen shot, which I'm unable to formulate. Any guidance will be a big help.

Calculations

Aging - Request Date

=Today() -
Max({<[Doc Status]={'New Request'}, [Is DF Document]={'No'},[Sub Doc Type]-={'VAL1', 'VAL2'},
[CP Doc: DocID]>}[Request Date])

Docs

=Count({<[Doc Status]={'New Request'}, [Is DF Document]={'No'},[Sub Document Type]-={'VAL1', 'VAL2'}>}[CP Doc: DocID])

Pivot Table Info

Will it be possible to create a pivot table of above data where Aging - Request Date is > 2?

[sunny_talwar · Accepted solution]

How about this:

=Sum(Aggr(If(Today() - Max({<[Doc Status] = {'New Request'}, [Is DF Document] = {'No'}, [Sub Doc Type] -= {'VAL1', 'VAL2'},
[CP Doc: DocID]>} [Request Date]) > 2, Count({<[Doc Status] = {'New Request'}, [Is DF Document] = {'No'}, [Sub Document Type] -= {'VAL1', 'VAL2'}>} [CP Doc: DocID])), Name, [Request Date]))

[vvira1316]

Hi Sunny,

Thanks for a quick reply. It worked.

Regards,

Vijay

[sunny_talwar]

Super