Group month by last modified in time selection

[hansNL]

Hello,

I hope somebody can help me.

In Qlik Sense I am trying to create an table to count the unique appointments per month, based on a certain selection date. I now have the issue that one appointment can be multiple times in my dataset, because the appointment (as well as the date it was planned for) can be modified.

In my selection I want to show the appointment in the month it was planned for (AppointmentDate) based on the modified date (AppointmentModified).

In Qlikview I have created an simplified sample dataset; you can find it attached to this post. Below the printscreen of my flat data table.

When my selection date is November 2nd 2016 the month I would like to show is January 2017 (the month of 2-1-2017).

When my selection date is February 14th 2017 the month I would like to show is February 2017 (left orange square in the printscreen).

When my selection date is May20th 2017 the month I would like to show is May 2017 (left blue square in the printscreen).

WHen my selection date is May 25th 2017 the month I would like to show is September 2017.

I tried to use firstsortedvalue in combination with an aggr on my appointmentid but I don't seem to get it working.

Can anybody help or advise what to do to get this correct?

Thanks in advance for your help and answers.

[p_verkooijen]

Maybe stalwar1 could help you out, seen several posts of him regarding the use of firstsorted value

[sunny_talwar]

One way would be to use something like this

=Sum(Aggr(If(Max(TOTAL <AppointmentID> If(AppointmentModifiedDT < vSelectionDate, YearMonth, 0)) = YearMonth, 1, 0), AppointmentID, AppointmentModifiedDT, AppointmentCreatedDT))

[hansNL]

Hi stalwar1‌,

Thanks for your reply. When I opened the file I thought this would be a working solution but when I added this into my report it unfortunately does not work.

In my file I have now 2 issues:

- There are appointments counted more than once. The filter on appointmentID 214227|42872|7 shows an appointment which is counted 16 times.

- The calculation is quite slow with the number of records which are in.

That's why I now created a new document including more example records. The file is attached. When you remove the filter you can see the reporting as it is now. In the row Sunny Calculation I added your calculation including some nescessary set analysis.

I hope you can advise how to solve this.

[sunny_talwar]

For number 1, I have not tested it with the complete dataset (not sure what the desired output is), but with the particular appointment id, this seems to be working

=Count(DISTINCT {<FactType={'AP'}, Date={">=$(vSelectionDate)<$(vSelectionDateNY)"}, AppointmentCreatedDate={"<=$(vSelectionDate)"}, AppointmentDeletedDate={">$(vSelectionDate)"}>}

Aggr(

If(Max({<FactType={'AP'}, Date={">=$(vSelectionDate)<$(vSelectionDateNY)"}, AppointmentCreatedDate={"<=$(vSelectionDate)"}, AppointmentDeletedDate={">$(vSelectionDate)"}>} TOTAL <AppointmentID> If(AppointmentModifiedDateTime < vSelectionDate, Month, 0)) = Month, AppointmentID, 0), AppointmentID, AppointmentModifiedDateTime, AppointmentCreatedDate))

With regards to the slowness, I think there is barely anything you can do about it. One possibility is to create a new field in the script with AppointmentID&AppointmentModifiedDateTime&AppointmentCreatedDate and then try with some sort of set analysis.

But see if the above expression works the way you would expect for the whole dataset? If it does, then we will worry about the performance part.

[hansNL]

Hi Sunny,

This is going into the right direction. I found some issues in the data as well so these I am cleaning up first before checking further if the formula calculates the correct result.

What I also have to figure out is why your formula does not give any results when I paste it into my Sense app. I guess this has to do with other rows in the same fact table. When I paste it in an Sense app with only this dataset does work.

[sunny_talwar]

Variables missing? Date has a different format? Could be anything... difficult to say

[hansNL]

Hi Sunny,

I just found out what the issue is and my assumption was correct. As soon as I select 'AP' (appointment) in the field FactType your calculation works. With this selection I only select the rows with valid data regarding the appointments.

It looks like the calculation cannot handle other non-appointment related data in the same fact table.

Nevertheless; even with this selection in place the calculation takes way to long; about 3 minutes. As soon as I change the selectiondate the new calculation again takes 3 minutes. So this is not acceptable for the end-users.

I hope you can advise on:

1. How to change the calculation so the other none-appointment related fact rows don't impact the calculation

2. How to improve calculation performance.

Thanks in advance.

[sunny_talwar]

1) You will have to share this none-appointment example where this isn't working. It would be difficult to figure out for me without knowing your data

2) I did propose the use of set analysis, but will have to played around with it. I think I will wait for your response on the first thing and then work on both the things at the same time

[hansNL]

Hi Sunny,

thanks for your help. I have spend some time on it but I am not able to find out which records are causing the issue that it won't work without selecting the Fact Type appointment. Because of this I cannot upload an example of this issue.

I have the idea that we / I was thinking to difficult so that's why I went back to the basics again.

Now I have created a new basic Qlikview document with another way of calculating it.

In the load script I have now created an field with an appointment ID ("AfsprRecId")  containing the id of the appointment and the record number of the appointment. So the first record for the appointment has as last digit a 1 and for each line where the appointment was modified the last number is + 1.

I have created an variable "vTest" which concats all the (for the current selection valid) maximum AfsprRecID's .

In the table I now do an distinct count of the appointments where the id exists in the variable.

This works in my test and also in my production app. The only issue is that the calculation time in my production app takes way, way to long.

Hopefully you can help me with integrating my variable into the set analysis directly.

Thanks for your help!