Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

partial sums issue

Hi Community,

I'm facing issue with partial sums, problem was have used Aggr calculated expression.

Please any one can help me.

Thanks in Advance

1 Solution

Accepted Solutions
Highlighted
Creator
Creator

Re: partial sums issue

Hello

This should do the trick:

sum(Aggr(IF(Rep_Dept='Con Ops',

(SUM(AGGR(SUM(TOTAL <Month, Rep_Dept> {<Week=>} ConOpsTarget), Week, Month, Rep_Dept))

/

NetWorkDays(MonthStart(MIN(Common_Date)), MonthEnd(Max(Common_Date)),  Date#('01/01/2018', 'MM/DD/YYYY'), Date#('03/30/2018', 'MM/DD/YYYY'),

Date#('04/02/2018', 'MM/DD/YYYY'), Date#('05/07/2018', 'MM/DD/YYYY'), Date#('05/28/2018', 'MM/DD/YYYY'), Date#('08/27/2018', 'MM/DD/YYYY'), Date#('12/25/2018', 'MM/DD/YYYY'), Date#('12/26/2018', 'MM/DD/YYYY')))

*

NetWorkDays(MIN(Common_Date), MAX(Common_Date), Date#('01/01/2018', 'MM/DD/YYYY'), Date#('03/30/2018', 'MM/DD/YYYY'),

Date#('04/02/2018', 'MM/DD/YYYY'), Date#('05/07/2018', 'MM/DD/YYYY'), Date#('05/28/2018', 'MM/DD/YYYY'), Date#('08/27/2018', 'MM/DD/YYYY'), Date#('12/25/2018', 'MM/DD/YYYY'), Date#('12/26/2018', 'MM/DD/YYYY')),

(SUM([Charge Rate] * [Weekly Time Cost Hours]) / 5) *

NetWorkDays(SUM(DISTINCT Aggr(NODISTINCT MIN({<[Staff Name]=, Rep_Dept=>}Common_Date), Week)), SUM(DISTINCT Aggr(NODISTINCT MAX({<[Staff Name]=, Rep_Dept=>}Common_Date), Week)),

Date#('01/01/2018', 'MM/DD/YYYY'), Date#('03/30/2018', 'MM/DD/YYYY'),

Date#('04/02/2018', 'MM/DD/YYYY'), Date#('05/07/2018', 'MM/DD/YYYY'), Date#('05/28/2018', 'MM/DD/YYYY'), Date#('08/27/2018', 'MM/DD/YYYY'), Date#('12/25/2018', 'MM/DD/YYYY'), Date#('12/26/2018', 'MM/DD/YYYY')))

,Month, Rep_Dept,Week,

//Common_Date,[Charge Rate],

//[Weekly Time Cost Hours],

[Staff Name]

)

)


View solution in original post

5 Replies
Highlighted

Re: partial sums issue

It seems that the attached dashboard has section access. Can you remove it and repost it

Highlighted
Creator
Creator

Re: partial sums issue

Please can you check now

Highlighted

Re: partial sums issue

I am able to open up, now can you tell us what exactly is the issue you are facing?

Highlighted
Creator
Creator

Re: partial sums issue

Hello

This should do the trick:

sum(Aggr(IF(Rep_Dept='Con Ops',

(SUM(AGGR(SUM(TOTAL <Month, Rep_Dept> {<Week=>} ConOpsTarget), Week, Month, Rep_Dept))

/

NetWorkDays(MonthStart(MIN(Common_Date)), MonthEnd(Max(Common_Date)),  Date#('01/01/2018', 'MM/DD/YYYY'), Date#('03/30/2018', 'MM/DD/YYYY'),

Date#('04/02/2018', 'MM/DD/YYYY'), Date#('05/07/2018', 'MM/DD/YYYY'), Date#('05/28/2018', 'MM/DD/YYYY'), Date#('08/27/2018', 'MM/DD/YYYY'), Date#('12/25/2018', 'MM/DD/YYYY'), Date#('12/26/2018', 'MM/DD/YYYY')))

*

NetWorkDays(MIN(Common_Date), MAX(Common_Date), Date#('01/01/2018', 'MM/DD/YYYY'), Date#('03/30/2018', 'MM/DD/YYYY'),

Date#('04/02/2018', 'MM/DD/YYYY'), Date#('05/07/2018', 'MM/DD/YYYY'), Date#('05/28/2018', 'MM/DD/YYYY'), Date#('08/27/2018', 'MM/DD/YYYY'), Date#('12/25/2018', 'MM/DD/YYYY'), Date#('12/26/2018', 'MM/DD/YYYY')),

(SUM([Charge Rate] * [Weekly Time Cost Hours]) / 5) *

NetWorkDays(SUM(DISTINCT Aggr(NODISTINCT MIN({<[Staff Name]=, Rep_Dept=>}Common_Date), Week)), SUM(DISTINCT Aggr(NODISTINCT MAX({<[Staff Name]=, Rep_Dept=>}Common_Date), Week)),

Date#('01/01/2018', 'MM/DD/YYYY'), Date#('03/30/2018', 'MM/DD/YYYY'),

Date#('04/02/2018', 'MM/DD/YYYY'), Date#('05/07/2018', 'MM/DD/YYYY'), Date#('05/28/2018', 'MM/DD/YYYY'), Date#('08/27/2018', 'MM/DD/YYYY'), Date#('12/25/2018', 'MM/DD/YYYY'), Date#('12/26/2018', 'MM/DD/YYYY')))

,Month, Rep_Dept,Week,

//Common_Date,[Charge Rate],

//[Weekly Time Cost Hours],

[Staff Name]

)

)


View solution in original post

Highlighted
Creator
Creator

Re: partial sums issue

Thank you