topic Re: Filtered date not recognised as date in QlikView

Filtered date not recognised as date

mattphillip — Sat, 16 Nov 2024 04:34:59 GMT

Hoping someone can help. I'm trying to produce a pivot table which is providing sub-total for applications based on the the final export date within a field called 'Date' which is filtered by an expression. The expression succeeds in pulling out the final export date per month for a particular session and is formatted as a date. However, the sub-totals are way higher than they should be which tells me that Qlikview doesn't recognise the filtered date as having values from the Date field. When I manually select a date within the Date field, the totals then look accurate.

Date filter expression: =date(aggr(max({<Session={'2018/19'}>}Date),Month),'DD/MM/YYYY')

Sub-Total expression: =count([Applicant ID])

Does anyone have any idea what I might be doing wrong?

Many thanks,

Matt

Re: Filtered date not recognised as date

sunny_talwar — Fri, 01 Feb 2019 12:34:10 GMT

In your current selections, are you seeing a selection in the Month field? or are you seeing selection in both month and date field?

Re: Filtered date not recognised as date

mattphillip — Fri, 01 Feb 2019 13:11:37 GMT

Hi Sunny,

Thanks for the quick reply. I don't have a month field as such, rather the expression is filtering a populated field labelled as date to pick out the last export dates for each month (rather than the last calendar date). The format for the Date field is DD/MM/YYYY.

M

Re: Filtered date not recognised as date

sunny_talwar — Fri, 01 Feb 2019 13:13:30 GMT

You don't have a month field?

But you have made a reference of your Month field here?

Re: Filtered date not recognised as date

mattphillip — Fri, 01 Feb 2019 14:42:47 GMT

Sunny, my apologies. I misread your email (Trying to multi-task in the office isn't the easiest thing!). Yes, for the purposes of the expression I created a month field in the load script ( Month(Date)as Month,).

Yes, when I click on one of the filtered dates in the pivot table, it is making a selection within both the 'Date' and 'Month' fields.

Re: Filtered date not recognised as date

sunny_talwar — Fri, 01 Feb 2019 14:58:23 GMT

Make sense, does it select a single date or is it selecting all dates within the month?

Re: Filtered date not recognised as date

mattphillip — Fri, 01 Feb 2019 14:59:47 GMT

All the export dates within that month.

Re: Filtered date not recognised as date

sunny_talwar — Fri, 01 Feb 2019 15:11:04 GMT

In that case, you might have to use a set analysis within your expression to display the Count related to the Max Date only.

Re: Filtered date not recognised as date

mattphillip — Fri, 01 Feb 2019 15:43:47 GMT

Right I've tried this with the code below but I'm still getting very high subtotals...

=count({$<Date={"=MaxString(Date)"}>}[Applicant ID])

Any ideas?

Re: Filtered date not recognised as date

sunny_talwar — Fri, 01 Feb 2019 15:46:26 GMT

Try something like this

=Count({$<Date = {"$(=MaxString(Date))"}>} [Applicant ID])

Re: Filtered date not recognised as date

mattphillip — Fri, 01 Feb 2019 15:56:02 GMT

Thanks Sunny! The subtotals are now looking accurate, although there seem to be a slight side effect in that, the pivot is not only showing one date, rather than showing the last export date for each month of the preceding year. Is there a way to tweak this to retain the accurate subtotals but to allow the pivot to also display the other month's export subtotals?

As always your advice is much appreciated.

Re: Filtered date not recognised as date

sunny_talwar — Fri, 01 Feb 2019 16:14:50 GMT

@mattphillip wrote:
the pivot is not only showing one date, rather than showing the last export date for each month of the preceding year.

I am not sure I follow this? Would you be able to elaborate on this with an example?

Re: Filtered date not recognised as date

mattphillip — Fri, 01 Feb 2019 16:24:29 GMT

Hi Sunny,

No problem. See below. This is what my pivot table currently looks like. It should instead feature multiple rows (for the last export of each month). Instead its listing only a single row. This can be changed by selecting a different date(s) but still only shows a single row.

End of Month Reports (2018/19)	Level	PG Research	PG Taught	Undergraduate
30/01/2018		2	118	3323

Re: Filtered date not recognised as date

sunny_talwar — Fri, 01 Feb 2019 16:26:53 GMT

Are you facing this issue when no selection is made? Because once a selection is made, you would only want to see a single row, right?

Re: Filtered date not recognised as date

mattphillip — Fri, 01 Feb 2019 16:28:01 GMT

Yes when no selection has been made the table is still only showing a single row rather than all the available rows.

Re: Filtered date not recognised as date

sunny_talwar — Fri, 01 Feb 2019 16:29:55 GMT

Try this expression

If(GetSelectedCount(Month) = 0,
   Count([Applicant ID]),
   Count({$<Date = {"$(=MaxString(Date))"}>} [Applicant ID])
)

So, basically we will use the beginning expression when nothing is selected and only when something is selected in Month field, we will see the new expression with set analysis.

Re: Filtered date not recognised as date

mattphillip — Fri, 01 Feb 2019 16:34:43 GMT

Thanks Sunny I tried this but whilst it does now show multiple rows, its also showing the incorrect (very high subtotals) again. When I select more than on month it will also only display a single row.

Sorry to be taking up so much of your time.

Re: Filtered date not recognised as date

sunny_talwar — Fri, 01 Feb 2019 16:59:19 GMT

I don't think I understand the issue you are running into. Would you be able to share a qvw/qvf sample to show the issue?

Re: Filtered date not recognised as date

mattphillip — Thu, 07 Feb 2019 11:41:23 GMT

Hi Sunny,

Apologies for the delay in getting back to you. I was out of the office for a couple of days and my PC was being replaced with a shiny new laptop upon my return so I'm catching up a bit. Let me look at where we're at and try and better identify what the remaining issue might be.

Thanks once again for all your help.

Matt

Re: Filtered date not recognised as date

mattphillip — Mon, 11 Feb 2019 16:21:02 GMT

Hi Sunny,

Right I've had a look at this again, and had another go. I've changed my approach slightly.

I've now went for a pivot table with one dimension (Level) and two expressions to achieve a table similar to the below:

Level	UG	PGT	PGR
End of <chosen month> (current session 2019/20)
End of <chosen month> (previous session - 2018/19)

The pivot includes two different expressions to count the applicants at the final export date of a chosen month and specified session:

=Count(max({$<Date = {"$(=vActiveSessionExp)"}>} [Applicant ID]))

=Count(max({$<Date = {"$(=vOldSessionExp)"}>} [Applicant ID]))

The variables above are populated as below to capture the the final export date for each month (not the final calendar date):

vActiveSessionExp

=date(aggr(max({$<Session={"$(=vActiveSession)"}>}Date),Month),'DD/MM/YYYY')

vOldSessionExp

=date(aggr(max({$<Session={"$(=vOldSession)"}>}Date),Month),'DD/MM/YYYY')

These variables in turn are populated as below to format the field vales for the Session field properly:

vActiveSession

=left(vCurrentSession,4)&'/'&right(vCurrentSession,2)

vOldSession

=left(vPreviousSession,4)&'/'&right(vPreviousSession,2)

These variables in turn are populated as below to capture the most recent and the second most recent session respectively:

vCurrentSession

= Max(TOTAL KeepChar(Session, '0123456789'), 1)

vPreviousSession

= Max(TOTAL KeepChar(Session, '0123456789'), 2)

Whilst I thought this might work, I'm getting an error message stating that 'Nested aggregation is not allowed'. If you have the time, would you mind taking a look to see where I've went wrong?

I tried the below expression in an attempt to fix it but keep getting the same message.

=Count(max({$<Date = {"$(=date(aggr(max({$<Session={"=left(aggr(Max(TOTAL KeepChar(Session, '0123456789'), 1),4)&'/'&right(aggr(Max(TOTAL KeepChar(Session, '0123456789'), 1),2)"}>}Date),Month),'DD/MM/YYYY'))"}>})[Applicant ID]))))

Appreciated.

Matt