Hi Team,
I created a networkdays formula in dashboard which is showing wrong count(1 count less compare to excel) when I compare to excel sheet. Due to this my TTR and TP90 are showing wrong data.
Thanks,
Hariprakash
Hi @hripraa - Not sure, but to help you in this case, can you please share the formula details ( both for QS and excel). This will help community member to guide you the right solution.
Regards - Sanjeeb
Hi @Sanjeeb2022
Sharing you the formula in below.
Dashboard formula: “netWorkDays(CreateDate,ResolvedDate)”
Excel formula: “=NETWORKDAYS(G3,H3)” (G3= created date and H3= resolved date)
Please note: my dashboard location is "US East(N. virginia)
For example: created date= 2024-05-03 13:20:25 and resolved date= 2024-05-07 00:02:18
Actually cycle days is 3 which is correct in excel but in dashboard which is showing as 2.
Is this because of the time zone?
Kindly help me on this
Hi @hripraa -I think you are correct, the calculation may affect the timezone? I sit possible to check the excel data in the same timezone as dashboard?
By saying this, let’s hear from other QS experts, tagging @David_Wong and @sagmukhe for their advise.
Regards - Sanjeeb
Hi @hripraa,
Your observation seems to be right. I created a sample dataset using the example data given and created a calculated field using the Networkdays function in the dataset and it produces a result of 3 which is the right answer.
However, when I look at the same data in the analysis the result is 2.
I then changed the sample dataset to remove the time portion in your dates. The result in the dataset and analysis produce 3 now!
So, the time portion is playing a role and the analysis seems to be doing something differently. If you use the truncDate to only use the date portion of your date you should get your result
HI @Giridhar.Prabhu,
As per your suggestion I changed the formula but still which is showing wrong cycle days.
For example: created date: 2024-05-09 00:04:01 and resolved date: 2024-05-09 22:43:47
Correct cycle day is “1” but in dashboard which is showing wrong as “2”.
Pls help me to resolve the issue.
Thanks
Hi @Giridhar.Prabhu and @Sanjeeb2022
Any updates?
Hi @hripraa,
I used the dates you shared in the test dataset I had created and both the dataset and analysis show 1 as the result.
Can you share your formula so I can take a look?
This is the formula i used:
netWorkDays(truncDate(‘DD’,CreateDate), truncDate(‘DD’,ResolvedDate))
Hi @Giridhar.Prabhu ,
Any updates?
Hi Team,
Any updates?
Please share sample output from your dataset and analysis to review
Hi @Giridhar.Prabhu ,
My networkdays formula working fine but my create date and resolve date were showing wrong date. Attaching the screenshot for your reference.
My dashboard create date (May 13, 2024) and resolve date (May 14, 2024) is
But my actually create date (May 14, 2024) and resolved date (May 14, 2024) is in below ticket.
Kindly help me to resolve this issue.
Thanks,
Any updates?
@hripraa Thanks for the additional details. So, the issue is that your source data is in India Time Zone (Kolkata/India). When the data is stored in dataset it is in UTC Timezone.
Taking your example see the Kolkata to UTC time zone conversion.
If you change your analysis time setting to Asia/Kolkata you should see data in your timezone and should see expected results.
Thank you so much for your help @Giridhar.Prabhu
