Show Only One Dimension value in Straight Table

[uko223]

Hi, 

I am trying to create a table in QlikView where the date is displayed as a dimension and the measures are sales for that corresponding day but also the prior year amount at that date. 

Key columns are

Fiscal Date:

Measures:

Actual

Prior Year

Both Measures are the same essentially except in the set analysis the "Actual" measure has "Current Month = Y" and the "Prior Year" measure has "Prior Year Current Month - Y". 

Currently, I have the measures for the current date and prior year all set but the issue is that in the fiscal date column, I need to only show the date of the "Actual" measure but it is showing both the "Actual" and "Prior Year" measure date.

See below example: 

Current:

Week 1	Fiscal - Cal Days Into Period	Fiscal - Date	Day	Actual	Prior Year
Week 1	1	2/25/2018	Sun	$0	$73,617
Week 1	1	2/24/2019	Sun	$10,746	$0

Need

Week 1	Fiscal - Cal Days Into Period	Fiscal - Date	Day	Actual	Prior Year
Week 1	1	2/24/2019	Sun	$10,746	$73,617

Is there a way I can use aggr/max to have the "Fiscal - Date" only show the date based on the actual measure?

Thanks!

[rwunderlich]

Have you tried

Date(Max([Fiscal - Date]))

-Rob

[hopkinsc]

What about..

Aggr(Max(Fiscal-Date),list all dims)

[uko223]

When I try that expression I get "Error in Calculated Dimension".

[hopkinsc]

I have this as a calculated dimension in one of my QVWs that works..

(aggr(max(Date([Date])),EventType,Company,PeopleCountry,Department,Name,[Is Current Employee]))

So try..

(aggr(max(date(Fiscal-Date)),Dim1, Dim2, Dim3, etc))

Obviously, Fiscal-Date needs to be your actual date field.