Filtered date not recognised as date

[mattphillip]

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

[mattphillip]

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.

[sunny_talwar]

---

@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?

[mattphillip]

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

[sunny_talwar]

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?

[mattphillip]

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

[sunny_talwar]

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.

[mattphillip]

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.

[sunny_talwar]

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?

[mattphillip]

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

[mattphillip]

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