Set analysis with aggr()- and max()-function for timestamp

[vkal12]

Dear community,

I am desperately looking for support regarding the following problem: I have a Qlik Sense app where data is refreshed every hour. This means that every hour new files are generated and uploaded to Qlik Sense. The raw data is showing the column "Interval" and different transport ID's for that specific intervall. I additionally created a column „Date“.

This is how my data looks like:

What I want to do is to count the number of transport ID’s for the latest file for each day and visualize this within in a bar chart for the weekdays of the actual week.

With the following formula I can get the maximum timestamp for each day:

=TimeStamp(MAX(Interval), 'DD.MM.YYYY hh:mm')

This part of the formula seems to work. I’ve now tried to count the transport ID’s with the following formula:

=COUNT({<Interval = {"=TimeStamp(aggr(max(Interval), Date), 'DD.MM.YYYY hh:mm')"}>} Transport)

The result looks like this:

Unfortunately this is not correct as it seems that the „min“. interval is picked for calculation.
I think I might have a mistake in my formula but I’ve tried out many things that I’ve read in other community threads without finding a suitable solution.

The correct solution should be the following in the end: 

Date	Latest file	Count (Transport)
25.09.2022	25.09.2022 23:02	4
26.09.2022	26.09.2022 09:02	2

I would be very grateful if someone can have a look at this and help me.

Thank you very, very much in advance!

[rubenmarin · Accepted solution]

Hi, the set analysis will be evaluated before the table, so all rows will apply the same set analysis (the timestamp of the max date will be applied for all dates.

You can try with:

Sum(Aggr(If(Interval=TimeStamp(MAX(TOTAL <Date> Interval), 'DD.MM.YYYY hh:mm'), Count(Transport)),Date, Interval))

I didn't tested, maybe there is some error.

[vkal12]

Hi rubenmarin,

thank you very much for your support! This was a great hint and the formula worked out perfectly without any errors.