Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld starts MONDAY! last chance to register is now ! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Edvin
Creator
Creator

Counting days between two dates, but wrong answer, when the dates are the same day

Hello all,

Got a problem. I'm counting days between two dates, I need to count how many network days are between the lowest date and the second to the lowest, grouped by ID.

The example:

IDCompany nameProcessSend date
2415976JMASENT2020.06.11
2415976LDNOT2020.06.11
2415976JMASENT2020.07.09
2415976LDNOT2020.07.09
2415976JMASENT2020.07.10
2415976LDDONE2020.07.10

 

My measure expression looks like this:

avg(networkdays(aggr(min([Send date]),[ID]),aggr(min([Send date],2),[ID])))

Now, somehow it skips the second date, which is equal to the 1st one '2020.06.11', and brings the result 21, which is between '2020.07.09' and '2020.06.11'

The outcome should look like this:

IDNetWorkDaysBetween
24159760

 

Thanks for the help!

20 Replies
Edvin
Creator
Creator
Author

@Kush  1st sheet. Table below, the 4th column, don't know, maybe I need to make an example .qvf file with english names?

The idea is, I want results for every USERID with some calculations, for example, how much did they got ID's, how much of the ID's are DONE and etc. The 4th columns is how much networkdays between 1st response.

The last sheet with name "Detali" is the excel sheet

@sunny_talwarThanks! But not working.. 😞

sunny_talwar

@Edvin  Is this what you want?

image.png

This is what I used

Avg(Aggr(
NetWorkDays(
	FirstSortedValue([Siuntimo data], Aggr([Siuntimo data]+Rank([Serviso pavadinimas])/1e10, [Bylos/Subbylos numeris], [Serviso pavadinimas], [Siuntimo data])),
	FirstSortedValue([Siuntimo data], Aggr([Siuntimo data]+Rank([Serviso pavadinimas])/1e10, [Bylos/Subbylos numeris], [Serviso pavadinimas], [Siuntimo data]), 2)
),
[Bylos/Subbylos numeris]))

 

Edvin
Creator
Creator
Author

@sunny_talwar  probably yes, thanks! I'm gonna check if it works correctly, but I think it will. My only question, what if there's no second lowest date? What if we don't have the response, will it take into account as '0' for average, or it's just NULL?

Kush
MVP
MVP

@Edvin  one change I did is to removed total. Does it gives correct at that level? What would be the output if you expand the second 

avg(aggr(NetWorkDays(min(total <[Bylos/Subbylos numeris]>{<DateFlag={1}>}[Siuntimo data]),
min(total <[Bylos/Subbylos numeris]>{<DateFlag={2}>}[Siuntimo data])), [Bylos/Subbylos numeris]))

View solution in original post

sunny_talwar

I am not entirely sure, but my guess is that it will try to call it null, but I might be wrong. @Kush or @Taoufiq_Zarra  do you guys know this?

Edvin
Creator
Creator
Author

@sunny_talwar  Oh, if you can check for the ID number = '2415235', why your expression shows '-' when the second lowest date and the lowest date are the same? 😞

sunny_talwar

@Edvin Updated expression

Avg(Aggr(
NetWorkDays(
	FirstSortedValue(DISTINCT [Siuntimo data], Aggr([Siuntimo data]+Rank(TOTAL [Serviso pavadinimas])/1e10, [Bylos/Subbylos numeris], [Serviso pavadinimas], [Siuntimo data])),
	FirstSortedValue(DISTINCT [Siuntimo data], Aggr([Siuntimo data]+Rank(TOTAL [Serviso pavadinimas])/1e10, [Bylos/Subbylos numeris], [Serviso pavadinimas], [Siuntimo data]), 2)
),
[Bylos/Subbylos numeris]))

Also, noticed that same date within NetWorkDays() function gives 1. If you want one less day, then do -1 at the end

View solution in original post

Edvin
Creator
Creator
Author

@Kush @sunny_talwar  thanks both, guys!

I would accept both your answers for solution, but sunny's expression work, without editing back-end.

Really appreciate, thanks again!

Kush
MVP
MVP

@sunny_talwar  Your updated expressions exactly gives same result as mine with bit of script logic. I would prefer to go with script logic Flag because firstsortedvalue function is always the resource consuming functions. So better to avoid the usage of it as far as possible. Bu I think @Edvin  can confirm if this figures are correct or not.

Kush
MVP
MVP

@Edvin  You said you will accept both solution but you did not :😀