topic Sum Logic in QlikView

Sum Logic

tmumaw — Thu, 22 Feb 2018 16:25:36 GMT

Hello All,

Need a little help on a calculation. On one pivot table I am showing Avg No. of employees by month and year. On the other pivot table I am showing the same data only adding Reason. I need to show the Avg No of employees on both tables. I am using the same calculation on both, but the one with reason added is showing something different. Here is my calculation I use on both pivot tables.

sum({<YTDFlag = {1}>}EmpCt_ZEMPCT) / Count({<EmpDays = {1}, YTDFlag = {1}>}DISTINCT(Date_ZWKDATE))

First pivot table is by Office, Year and Month and the other is by Office, Year, Month and Reason.

Re: Sum Logic

sunny_talwar — Thu, 22 Feb 2018 16:35:11 GMT

I guess you need to start with reading this

Average – Which average?‌

Re: Sum Logic

Digvijay_Singh — Thu, 22 Feb 2018 16:37:20 GMT

If you want same result on both tables then use this Total keyword without Reason included-

sum(Total<Office,Year,Month> {<YTDFlag = {1}>}EmpCt_ZEMPCT) / Count(Total<Office,Year,Month> {<EmpDays = {1}, YTDFlag = {1}>}DISTINCT(Date_ZWKDATE))

Re: Sum Logic

tmumaw — Thu, 22 Feb 2018 17:55:16 GMT

Here is what I am seeing when I use you expression.

Re: Sum Logic

Digvijay_Singh — Thu, 22 Feb 2018 18:27:43 GMT

Not sure what is making this difference, although Aggr is slow, can u see result of this -

Aggr(sum( {<YTDFlag = {1}>}EmpCt_ZEMPCT) /Count( {<EmpDays = {1}, YTDFlag = {1}>}DISTINCT(Date_ZWKDATE)),Office,Year,Month)

If you can share sample data, would help to identify the real problem..

Re: Sum Logic

tmumaw — Thu, 22 Feb 2018 18:36:31 GMT

Aggr results look better, but look at last line on the second pivot table (No Results?)

Re: Sum Logic

Digvijay_Singh — Thu, 22 Feb 2018 18:57:15 GMT

Try this -

Aggr(NODISTINCT sum( {<YTDFlag = {1}>}EmpCt_ZEMPCT) /Count( {<EmpDays = {1}, YTDFlag= {1}>}DISTINCT(Date_ZWKDATE)),Office,Year,Month)

Re: Sum Logic

Digvijay_Singh — Thu, 22 Feb 2018 19:01:40 GMT

Hi swuehl‌

Any reason Total is showing different results here?

Re: Sum Logic

sunny_talwar — Thu, 22 Feb 2018 19:05:26 GMT

NODISTINCT should solve the problem

Re: Sum Logic

tmumaw — Thu, 22 Feb 2018 19:07:44 GMT

Thank you very much for all your help. It appears that "NODISTINCT" fixed the problem

Re: Sum Logic

Digvijay_Singh — Thu, 22 Feb 2018 19:14:31 GMT

I mean why TOTAL and AGGR are showing different results?

Re: Sum Logic

sunny_talwar — Thu, 22 Feb 2018 21:06:44 GMT

Have been seen this issue in some of the cases now. Not sure what causes is, but sometime it happens when there is a calculated dimension involved.

Re: Sum Logic

swuehl — Thu, 22 Feb 2018 21:48:50 GMT

May your Reason show NULL?

Re: Sum Logic

pablolabbe — Thu, 01 Mar 2018 00:51:17 GMT

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others