topic Re: Networkdays issue in QlikView

topic Re: Networkdays issue in QlikView https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67084#M773557 <HTML><HEAD></HEAD><BODY><P>so for StaffName = A, you want to see 21.75 for first 4 weeks and 13.04 for the 5th fifth week when you select Jan?</P></BODY></HTML> Tue, 22 May 2018 09:09:36 GMT sunny_talwar 2018-05-22T09:09:36Z Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67077#M773543 <HTML><HEAD></HEAD><BODY><P>Hi Community,</P><P></P><P>I'm facing one issue with working days.</P><P>My calculation like:</P><P><SPAN style="color: #ff0000;"><SPAN style="color: #ff0000;">=(Sum(sales)</SPAN> / Number of working days in a month) * Number of working days in week</SPAN></P><P></P><P><SPAN style="color: #000000;">See below image, Monthly working days working fine, but weekly working days not working properly..</SPAN></P><P><SPAN style="color: #000000;">Example : 18 week, Monthly working days =23 and Weekly WDays should be 4., but here showing 1.</SPAN></P><P></P><P><SPAN style="color: #000000;">My Sales2 column should be 375</SPAN></P><P><SPAN style="color: #ff0000;"><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/202969_Capture.PNG" style="height: auto;" /></SPAN></P><P><SPAN style="color: #000000;">PFA, </SPAN></P><P></P><P><SPAN style="color: #000000;">Please can you help Experts!</SPAN></P><P></P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67077#M773543 paulwalker 2020-11-25T16:16:04Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67078#M773546 <HTML><HEAD></HEAD><BODY><P>May be this</P><P></P><P><STRONG>NetWorkDays(MIN(TOTAL <Week> Common_Date), MAX(TOTAL <Week> Common_Date))</STRONG></P></BODY></HTML> Mon, 21 May 2018 14:13:01 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67078#M773546 sunny_talwar 2018-05-21T14:13:01Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67079#M773548 <HTML><HEAD></HEAD><BODY><P>Hi Sunny,</P><P>Thanks for reply..</P><P>18th week we have 4 working days, but 19, 20, 21 have 5 working days, </P><P>here showing 4 only, should be 5</P><P></P><P>My selections are 2018 May</P><P><IMG alt="Capture.JPG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/202976_Capture.JPG" style="height: auto;" /></P></BODY></HTML> Mon, 21 May 2018 14:27:50 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67079#M773548 paulwalker 2018-05-21T14:27:50Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67080#M773550 <HTML><HEAD></HEAD><BODY><P>Why is Week 22 have 5 work days?</P><P></P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/202977_Capture.PNG" style="height: 202px; width: 620px;" /></P><P></P><P>Week 19, can be fixed using this</P><P><STRONG>NetWorkDays(Aggr(NODISTINCT MIN(Common_Date), Week), Aggr(NODISTINCT MAX(Common_Date), Week))</STRONG></P><P><STRONG><BR /></STRONG></P><P><STRONG><IMG alt="Capture.PNG" class="jive-image image-2" src="https://community.qlik.com/legacyfs/online/202978_Capture.PNG" style="height: 142px; width: 620px;" /><BR /></STRONG></P></BODY></HTML> Mon, 21 May 2018 15:10:25 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67080#M773550 sunny_talwar 2018-05-21T15:10:25Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67081#M773552 <HTML><HEAD></HEAD><BODY><P>Excellent Job - Sunny <IMG src="https://community.qlik.com/legacyfs/online/emoticons/happy.png" /></P></BODY></HTML> Mon, 21 May 2018 15:33:46 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67081#M773552 paulwalker 2018-05-21T15:33:46Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67082#M773553 <HTML><HEAD></HEAD><BODY><P>Hi Sunny,</P><P>I need one more help..</P><P></P><P>I have StaffName, so if StaffName=A, i have to show Budget data.</P><P>But, Budget data not in proper way - monthwise data as like below.</P><P><IMG __jive_id="203025" alt="Capture.JPG" class="jive-image image-1" height="38" src="https://community.qlik.com/legacyfs/online/203025_Capture.JPG" style="height: 38.429px; width: 627px;" width="627" /></P><P>I written like:  <STRONG>SUM(AGGR(SUM(TOTAL  BudgetAmount), Week)),</STRONG></P><P><STRONG>My data should be Bifurcate week level..</STRONG></P><P><STRONG>Suppose My JAN month have (W1(5Days), W2<STRONG style="font-size: 13.3333px;">(5Days)</STRONG>, W3<STRONG style="font-size: 13.3333px;">(5Days)</STRONG>,W4<STRONG style="font-size: 13.3333px;">(5Days)</STRONG>, adn W5<STRONG style="font-size: 13.3333px;">(3Days)</STRONG>)<BR /></STRONG></P><P><STRONG>=Week1 should be</STRONG></P><DIV>=(100/(Number of working days in a month) )* Number of working days in a week</DIV><DIV>=(100/23)*5</DIV><DIV>=21.75</DIV><DIV><P style="font-size: 13.3333px;"><STRONG>=Week2 should be</STRONG></P><P style="font-size: 13.3333px;">=(100/(Number of working days in a month) )* Number of working days in a week</P><P style="font-size: 13.3333px;">=(100/23)*5</P><P style="font-size: 13.3333px;">=21.75</P><P style="font-size: 13.3333px;"><STRONG>=Week3 should be</STRONG></P><P style="font-size: 13.3333px;">=(100/(Number of working days in a month) )* Number of working days in a week</P><P style="font-size: 13.3333px;">=(100/23)*5</P><P style="font-size: 13.3333px;">=21.75</P><P style="font-size: 13.3333px;"><STRONG>=Week4 should be</STRONG></P><P style="font-size: 13.3333px;">=(100/(Number of working days in a month) )* Number of working days in a week</P><P style="font-size: 13.3333px;">=(100/23)*5</P><P style="font-size: 13.3333px;">=21.75</P><P style="font-size: 13.3333px;"><STRONG>=Week5 should be</STRONG></P><P style="font-size: 13.3333px;">=(100/(Number of working days in a month) )* Number of working days in a week</P><P style="font-size: 13.3333px;">=(100/23)*3</P><P style="font-size: 13.3333px;">=13.04</P><P style="font-size: 13.3333px;"></P><P style="font-size: 13.3333px;">Weeks totals (W1 to W5) should be <STRONG>100</STRONG></P></DIV><P><IMG __jive_id="203027" alt="Capture.JPG" class="jive-image image-2" src="https://community.qlik.com/legacyfs/online/203027_Capture.JPG" style="height: 92px; width: 620px;" /></P><P></P><P>Please can you help me on this....</P></BODY></HTML> Tue, 22 May 2018 05:32:07 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67082#M773553 paulwalker 2018-05-22T05:32:07Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67083#M773555 <HTML><HEAD></HEAD><BODY><P>Please anyone can help on this..!!!</P></BODY></HTML> Tue, 22 May 2018 07:58:36 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67083#M773555 paulwalker 2018-05-22T07:58:36Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67084#M773557 <HTML><HEAD></HEAD><BODY><P>so for StaffName = A, you want to see 21.75 for first 4 weeks and 13.04 for the 5th fifth week when you select Jan?</P></BODY></HTML> Tue, 22 May 2018 09:09:36 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67084#M773557 sunny_talwar 2018-05-22T09:09:36Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67085#M773558 <HTML><HEAD></HEAD><BODY><P>Yes sunny..</P><P>I'm trying with this, but not working properly... by default taking MAX values for all weeks.</P><P><STRONG>AGGR(RangeSum(TOP(TOTAL MAX(ConOpsTarget), 1, Week)), Week)</STRONG></P></BODY></HTML> Tue, 22 May 2018 09:13:52 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67085#M773558 paulwalker 2018-05-22T09:13:52Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67086#M773560 <HTML><HEAD></HEAD><BODY><P>I think this is a missing data problem... look what happens when I select A...</P><P></P><P><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/203044_Capture.PNG" style="height: auto;" /></P><P></P><P>There is no Week 3 and 4.... in order to fix this... you will need to fill in the missing information</P><P><A href="https://community.qlik.com/docs/DOC-3786">Generating Missing Data In QlikView</A></P></BODY></HTML> Tue, 22 May 2018 11:07:33 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67086#M773560 sunny_talwar 2018-05-22T11:07:33Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67087#M773562 <HTML><HEAD></HEAD><BODY><P>HI Sunny,</P><P></P><P>Please find the attachment, when i select single month working fine.</P><P>If i select couple of months getting wrong result..</P><P></P><P>If i make more than one month should not club the data...</P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">Please can you have a look...</SPAN></P></BODY></HTML> Tue, 22 May 2018 11:34:36 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67087#M773562 paulwalker 2018-05-22T11:34:36Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67088#M773565 <HTML><HEAD></HEAD><BODY><P>This almost works... but since week 5 is both in Jan and Feb... it is not able to pull the right value</P><P></P><P><SPAN style="font-size: 13.3333px;"><STRONG>IF([Staff Name]='A', </STRONG></SPAN></P><P><STRONG style="font-size: 13.3333px;">(SUM(AGGR(SUM(TOTAL <SPAN style="color: #ff0000;"><Month></SPAN>  BudgetAmount), Week<SPAN style="color: #ff0000;">, Month</SPAN>)) / NetWorkDays(MonthStart(MIN(Common_Date)), MonthEnd(Max(Common_Date))))</STRONG></P><P><STRONG style="font-size: 13.3333px;">*</STRONG></P><P><STRONG style="font-size: 13.3333px;">NetWorkDays(Aggr(NODISTINCT MIN({<[Staff Name]=>}Common_Date), Week), Aggr(NODISTINCT MAX({<[Staff Name]=>}Common_Date), Week)) ,</STRONG></P><P><STRONG style="font-size: 13.3333px;"><BR /></STRONG></P><P><SPAN style="font-size: 13.3333px;"><STRONG>SUM(Sales))</STRONG></SPAN></P><P><SPAN style="font-size: 13.3333px;"><STRONG><BR /></STRONG></SPAN></P><P><SPAN style="font-size: 13.3333px;"><STRONG><IMG alt="Capture.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/203054_Capture.PNG" style="height: auto;" /><BR /></STRONG></SPAN></P></BODY></HTML> Tue, 22 May 2018 11:38:58 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67088#M773565 sunny_talwar 2018-05-22T11:38:58Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67089#M773567 <HTML><HEAD></HEAD><BODY><P>Sample application working fine, but my main application not working.</P><P>If i select that particular <STRONG>StaffName (ex A)</STRONG> only working fine.</P><P></P><P>Any idea ?</P></BODY></HTML> Tue, 22 May 2018 12:07:18 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67089#M773567 paulwalker 2018-05-22T12:07:18Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67090#M773569 <HTML><HEAD></HEAD><BODY><P>Did you fix the problem of missing data in your original app?</P></BODY></HTML> Tue, 22 May 2018 12:12:54 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67090#M773569 sunny_talwar 2018-05-22T12:12:54Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67091#M773571 <HTML><HEAD></HEAD><BODY><P>Yes I did.</P></BODY></HTML> Tue, 22 May 2018 12:17:17 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67091#M773571 paulwalker 2018-05-22T12:17:17Z Re: Networkdays issue https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67092#M773572 <HTML><HEAD></HEAD><BODY><P>Don't know</P></BODY></HTML> Tue, 22 May 2018 12:27:24 GMT https://community.qlik.com/t5/QlikView/Networkdays-issue/m-p/67092#M773572 sunny_talwar 2018-05-22T12:27:24Z