topic Re: Show Only One Dimension value in Straight Table in QlikView

Show Only One Dimension value in Straight Table

uko223 — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Show Only One Dimension value in Straight Table

rwunderlich — Wed, 06 Mar 2019 15:07:09 GMT

Have you tried

Date(Max([Fiscal - Date]))

-Rob

Re: Show Only One Dimension value in Straight Table

hopkinsc — Wed, 06 Mar 2019 15:08:47 GMT

What about..

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

Re: Show Only One Dimension value in Straight Table

uko223 — Wed, 06 Mar 2019 15:21:35 GMT

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

Re: Show Only One Dimension value in Straight Table

hopkinsc — Wed, 06 Mar 2019 15:28:05 GMT

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.