Exclude field selection using set analysis not working

[Jesh19]

Hi All,

I've created two pivot tables.

In the first table, my dimension is YEAR & QTR and i have made them as columns.

In the second table, my dimension is only YEAR.

\Below is the screenshot.

My problem is  i don't want the values to get changed in 2nd table when i am selecting quarter field.

For No of NPL Programs - Count ({<QTR=>}Distinct PRG_ID) is working fine

But for NPL Programs < 6 months

=Count({<QTR=>}DISTINCT

If((Aggr((Year(Date(SHIPDATE,'DD/MM/YYYY'))*12)+month(Date(SHIPDATE,'DD/MM/YYYY')),PRG_ID)-Aggr((Year(Date(ORDERDATE,'DD/MM/YYYY'))*12)+month(Date(ORDERDATE,'DD/MM/YYYY')),PRG_ID))<=6, PRG_ID))

The above expression is changing upon the selection of QTR.

I want the values to be for the current fiscal year in the second table instead of changing upon selections.

Could someone please help me in solving the issue.

Thanks in Advance.

Regards,

Jeshwanth

[Anil_Babu_Samineni]

Could you post test application. With only() function should work..

[Jesh19]

Stefan,

It's working without date formatting.

Thanks.