topic Calculation based on the latest file without deleting duplicates in App Development

Calculation based on the latest file without deleting duplicates

vkal12 — Thu, 22 Sep 2022 12:23:53 GMT

Hello community members,

I am stuck on a very problem where I need to change the calculation based on the specific question. I have a use case where we upload a new file with the same structure to Qlik Sense every hour. Those files contain a dedicated field "intervall" with this format "DD.MM.YYYY hh:mm". This field shows the time the file was created. Now I want to create three different charts that show the following things:

a) Number of transports within the latest file: I figured out how that works and I use the following formula:

COUNT({< [intervall] = {"$(=TimeStamp(Max(TimeStamp#([intervall], 'DD.MM.YYYY hh:mm')), 'DD.MM.YYYY hh:mm'))"}>} transportid)

b) Number of transports for each weekday: Therefore I need to pick the latest file for each weekday (e.g. Monday at 11:59pm, Tuesday at 11:59 pm, ...) and then count the transportid's as shown in case a. The problem is, that I tried out a couple of things using an adjusted formula but I cannot get it to work.

c) Number of transports with in the last four weeks: Therefore I need to pick the latest file (e.g. Friday at 11:59 pm) for each week. Same as in case b: I tried out multiple thinks but I guess it is to komplex for me.

I hope someone can help me with this problem and help me loosen the knot in my brain 🙂

Thank you very much!

Re: Calculation based on the latest file without deleting duplicates

SerhanKaraer — Thu, 22 Sep 2022 20:27:42 GMT

Hello,

for b, you can try:

COUNT({< [intervall] = {"=weekday(intervall)>=1"} * {"=weekday(intervall)<=5"} * {"=hour(intervall)=23"}>} transportid)

assuming there is a file generated at 11 pm everyday.

for c, you can try:

COUNT({< [intervall] = {"=weekday(intervall)=5"} * {"=hour(intervall)=23"} * {"=floor(intervall)>=today()-28"}>} transportid)

assuming there is a file generated at 11 pm everyday.

Regards,

Re: Calculation based on the latest file without deleting duplicates

vkal12 — Fri, 23 Sep 2022 06:00:23 GMT

Hi SerhanKaraer,

thanks for your response! There will be definitely be a file generated at 11pm every day. But the dashboard gets updated every hour as well so that it is not possible to put an absolute number in that formular. For e.g. now it is 07:54 am and the last file ist from 07:00 am so that the calculation should be based on the latest file which will be from 07:00 am.

For the past days the latest file is always from 11pm but not for the current day. How can I include that in your suggested formula?

I extracted a new field from my field "intervall" which shows the hour, e.g. 1, 2, 3 and so on. From hat new filed I basically need to extract the max value and use that for my calculation. But this does not seem to work...

=COUNT({< [intervall] = {"$(=max([intervall]))"} >} transportid)

Kind regards,

Vanessa

Re: Calculation based on the latest file without deleting duplicates

SerhanKaraer — Fri, 23 Sep 2022 06:56:59 GMT

Hello,

It is good to hear that you can manipulate data model in script. You had better create a date of intervall field like date(floor(intervall)) as intervall_date.

Then for b, you can try:

COUNT({< [intervall] = {"=weekday(intervall)>=1"} * {"=weekday(intervall)<=5"} * {"=intervall=aggr(max(total <intervall_date> intervall),intervall_date)"}>} transportid)

Then for c, you can try:

COUNT({< [intervall] = {"=weekday(intervall)=5"} * {"=intervall=aggr(max(total <intervall_date> intervall),intervall_date)"} * {"=intervall_date>=today()-28"}>} transportid)

Regards,

Re: Calculation based on the latest file without deleting duplicates

vkal12 — Fri, 23 Sep 2022 08:49:28 GMT

Hi SerhanKaraer,

I've just tried out your new formula and unfortunately it is not working. Maybe I should show you my output and give you some more information.

This is how my data looks at the beginning:

For example b) my graph should look like this in the end with using the following formula:

=COUNT({< [Intervall] = {"=weekday([Intervall])>=0"} * {"=weekday(Intervall])<=5"}>} transportid)

This seems to work and looks good. The problem now is that it is summing up all the transport ids for the specific day. For today (Fr) we had nine incoming files each with 23067 transport ids, resulting in 207603 transport ids in total. I already have a Date Field included in my data model, just with a little bit different formula:

If I use your suggested formuala then it will look like this.

=COUNT({<[Intervall] = {"=weekday(Intervall)>=0"} * {"=weekday(Intervall)<=5"} * {"=Intervall=aggr(max(total Key_Datum Intervall]), Key_Datum)"}>} transportid)

Unfortunately I do not get any kind of values back. Therefore my graph is of course not showing anything relevant as well. Do you have any other idea?

Thank you very much!