80 Replies Latest reply: Nov 6, 2017 1:18 PM by VIJAY VIRA

# Problem summing an average in pivot table

I have a pivot table with a measure that calculates the average number of boxes shipped per day by each warehouse.  I'd like to get a sum of those averages by warehouse so I can then do a percentage of total calculation.  The current expression to get average of boxes per day is:

I've tried modifying that expression in order to get the total for all warehouses but haven't been able to get any variations to work as I expect.

etc.

• ###### Re: Problem summing an average in pivot table

May be try this

• ###### Re: Problem summing an average in pivot table

Blank result.

Please note that PFWarehouse is a Master item expression.  I'm using "Warehouse" in my calculations; PFWarehouse is meant to evaluate to Null if the warehouse is not in a particular match list.  So I updated your suggested formula to use Warehouse instead but got a blank result.

• ###### Re: Problem summing an average in pivot table

• ###### Re: Problem summing an average in pivot table

Yes, I did try that and that's when I got the blank result.

• ###### Re: Problem summing an average in pivot table

Would you be able to share a sample?

• ###### Re: Problem summing an average in pivot table

I've attached the QVF file. Please see the "Boxes" sheet and the pivot table called "Boxes Loaded Per Day (Avg).  The pivot table is a Master Item called Boxes Loaded Per Day - Pivot.

The expression for Boxes Per day is stored in a variable called vBoxesLoadedPerDay

I also created a variable for the total of all warehouses called vBoxesLoadedPerDay_AllWarehouses

Thanks.

• ###### Re: Problem summing an average in pivot table

Don't see Boxes sheet... am I missing something obvious

• ###### Re: Problem summing an average in pivot table

Probably attached an out-dated file.  I'll delete the attachment and post again in a few minutes.

• ###### Re: Problem summing an average in pivot table

Attachment has been updated.

• ###### Re: Problem summing an average in pivot table

Don't see the attachment

• ###### Re: Problem summing an average in pivot table

I guess it's still loading?  If it doesn't show up in a few minutes I'll upload again.

• ###### Re: Problem summing an average in pivot table

Updated attachment should be there now.

• ###### Re: Problem summing an average in pivot table

Hi Sunny.  Have you had a chance to look at the new attachment I loaded?  Thank you.

.

• ###### Re: Problem summing an average in pivot table

Hi,

 PFWarehouse Avg Boxes Loaded Per Day Total % HUR 125,098 928,151 13.48% SAL 139,650 928,151 15.05% SBF 131,870 928,151 14.21% SSS 134,232 928,151 14.46% YBF 148,953 928,151 16.05% YSS 123,223 928,151 13.28% YUM 125,125 928,151 13.48%

Total is sum of each of those individual values for warehouse

• ###### Re: Problem summing an average in pivot table

That is the behavior I'm looking for.  Are you able to replicate that behavior in a pivot table?

• ###### Re: Problem summing an average in pivot table

Hi Mike,

I/m not sure how I got that screen shot. Unfortunately I didn't save it and lost it when I came in next morning. I'll keep trying and let you know if I succeed in replicating.

Sorry

BR,

Vijay

• ###### Re: Problem summing an average in pivot table

Will try to check it out today.

• ###### Re: Problem summing an average in pivot table

Hi Sunny,

Hoping you have had an opportunity to review this a little more?  Thanks!

• ###### Re: Problem summing an average in pivot table

Nope, checking it now... should get back to you in 10 mins with questions (if I have any)

• ###### Re: Problem summing an average in pivot table

Thanks, I appreciate your help very much!

• ###### Re: Problem summing an average in pivot table

Try this

• ###### Re: Problem summing an average in pivot table

That expression gives the overall average for all warehouses.  I'm looking for the sum of the warehouse averages.  In your screenshot, that would be the sum of HUR, SAL, SBF, SSS, YBF, YSS, and YUM.  The grand total of the warehouses is 907,342.

• ###### Re: Problem summing an average in pivot table

Change Avg to Sum

• ###### Re: Problem summing an average in pivot table

I should have mentioned that I tried Sum instead of Avg in your expression and I got the number for the entire year instead of a daily avg total.  The number I'm getting is 39,605,548.

Thanks.

• ###### Re: Problem summing an average in pivot table

Hi Sunny,

I've tried multiple variations of expressions and still not able to get it to do what I want.

I even tried wrapping the daily average expression (vBoxesLoadedPerDay) in a Sum(Aggr()) and got interesting results but not what I'm looking for.  Any other suggestion you can think of?

Thank you!

• ###### Re: Problem summing an average in pivot table

Hi, doesn't the total number of boxes loaded help, as below? After all, you do want the sum of all boxes loaded, regardless of PFWarehouse, Year, Date

• ###### Re: Problem summing an average in pivot table

Logically that seems like it should work; unfortunately, it gives a blank result.

• ###### Re: Problem summing an average in pivot table

Hi,

Are you expecting following information?

• ###### Re: Problem summing an average in pivot table

Hi Vijay,

No, the totals you have look like the totals for the whole year.  I'm trying to get a total of all of the daily averages for each warehouse.  Thanks.

• ###### Re: Problem summing an average in pivot table

Hi Mike

I think I found the expression you're looking for, please check out the attachment and see if it suits your problem.

The expression I used is:

=sum(TOTAL <Year> aggr(avg(#Boxes),PFWarehouse,Year))

KR,

Cristina

• ###### Re: Problem summing an average in pivot table

Cristina,

I modified your expression to fit my data model and came up with

This appears to be an average for the year for each warehouse, so I changed it to go by date

The result is similar to one I tried with Sunny earlier and gives the wrong totals.  In the screen shot, note that the numbers are much too low.  The total for all warehouse averages should be 907,342.

• ###### Re: Problem summing an average in pivot table

Can you please send/attach your QVW file and maybe I can work on that directly. I'll try to reach this total of 907,342, to be sure.

Thanks!

Cristina

• ###### Re: Problem summing an average in pivot table

I posted a .qvf on 10/9, but here is the latest version.

Please see the "Boxes" sheet and the pivot table called "Boxes Loaded Per Day (Avg).  The pivot table is a Master Item called Boxes Loaded Per Day - Pivot.

The expression for Boxes Per day is stored in a variable called vBoxesLoadedPerDay

I also created a variable for the total of all warehouses called vBoxesLoadedPerDay_AllWarehouses; this is the variable that will hold the expression for the sum of all warehouse averages.  Note that PFWarehouse is used in the pivot table; this is a Master dimension that selects specific warehouses for this sheet.  Esxpressions should use Warehouse as the dimension name.

Thanks for having a go at this.

• ###### Re: Problem summing an average in pivot table

Are you confident that the row level numbers that you get are the correct numbers you are looking to get? It seems weird that the chart sort of works the way it does.

• ###### Re: Problem summing an average in pivot table

Yes, I am confident that the daily average numbers are correct.  If you drill down on another page, like Sales Order Metrics, and single out a date in a busy period, such as 8/9/2017, you'll see that the Shipped Qty is 139,572 for the SAL warehouse, which correlates to the averages we are seeing on the Boxes sheet.

• ###### Re: Problem summing an average in pivot table

I never used qvf files. So far I worked only with Qlikview files. I get  this is a Qlik Sense file, right?

• ###### Re: Problem summing an average in pivot table

Yes, it is Qlik Sense.

• ###### Re: Problem summing an average in pivot table

Hi Mike,

The total you are expecting includes totals number (125,334). I think it should not include that so the number you should be expecting to see should be 782,009 or so. Assuming your numbers are correct.

Another thought to validate number is to just create a straight table with following data points

Year, Date, ReportType, Warehouse, UnitsLoaded ( These are the data points that are getting used in the formula)

Download this table in excel to confirm numbers and may be just use that table by itself in newer application to validate. Or may be use your source to just have only that data to see if your numbers are matching.

I tried doing it but numbers are no where near so not sure if there is something missing here.

BR,

Vijay

• ###### Re: Problem summing an average in pivot table

You are correct about the total being too high; thanks for catching that.  I will take a look at your .qvf and get back to you.  Thank you!

• ###### Re: Problem summing an average in pivot table

I took a look at your qvf and did not find anything other than the original formula for the average by warehouse.  I suppose you just wanted confirmation of my totals?  Anyway, I have verified my numbers in a straight table and I was able to do a sum total line in a straight table.  I have attached a screen shot of that table showing the correct total is 785,610 for year 2014.

Thanks.

• ###### Re: Problem summing an average in pivot table

Mike,

Yes, I didn't find anything else. I just wanted to use 4 or 5 data points of formula by itself. The raw data. Would it be possible for you to just provide that in an excel from your source directly.

What I tried was that I created a straight table with these data points on a new sheet. When I exported that and created new app using exported information the numbers are too low. Numbers shouldn't be that low. Hence we should just try the pivot table in a new app where only these data points are available

BR

• ###### Re: Problem summing an average in pivot table

Can you download the qvf I provided in the discussion earlier?  There's one that I posted within the last 24 hours.

• ###### Re: Problem summing an average in pivot table

Hi Mike,

Anyway I have created a pivot table and a straight table in your file on sheet name verification.

I'm using the expression directly for Avg Boxes Loaded

Not sure why there are differences in some numbers as can be seen from screen shot below.

• ###### Re: Problem summing an average in pivot table

Try

• ###### Re: Problem summing an average in pivot table

Thanks for the suggestion but this has been tried already and does not work.

• ###### Re: Problem summing an average in pivot table

Those differences may be due to not having the full dataset loaded, but that's just a guess.  Perhaps I can output the main CoolerOut table to a QVD for you?  You might also need the Calendar table but I don't think you'll need Receipts, ItemDescriptions, etc  Let me know if you want the QVDs and I can attach them.

• ###### Re: Problem summing an average in pivot table

Hi Mike,

If it is ok for you to provide QVD then that is okay, otherwise excel, txt is fine as well. No need of other information, only date, reporttype, unitsloaded, warehouse will be sufficient.

BR,

Vijay

• ###### Re: Problem summing an average in pivot table

Here's an Excel spreadsheet that contains pertinent data; four periods from one year.  Hope that helps.

• ###### Re: Problem summing an average in pivot table

Hi Mike,

In the attached excel file there is no UnitLoaded field. That is fine. I'm using ShipQty instead. I'm unable to get the formula working.

Have you been successfully calculate it in Excel? If so, would you be able to share it? The excel formula may provide some new thoughts/directions as we have exhausted possible ideas. It would be nice to have it for a year, say 2014. The raw data, the sum by day, aggregated by, etc. etc. to arrive at final expected output along with final values

Good Luck.

BR,

Vijay

• ###### Re: Problem summing an average in pivot table

Hi Vijay.

ShipQty is the same as UnitsLoaded (it's just a Sum(UnitsLoaded) in the table that I exported to the Excel file).  I couldn't send a full year of data due to the row limitation in Excel.  I'll look at creating a QVD instead so you can get all the data needed for this calculation to work.

• ###### Re: Problem summing an average in pivot table

Hi Mike,

QVD is not required. I was just trying to see if you were to do that calculation in an Excel Worksheet, how you would have done that. Understanding that would have allowed to come up with logic in Qlik.

BR,

Vijay

• ###### Re: Problem summing an average in pivot table

I have not attempted to do the same thing in an Excel workbook.

• ###### Re: Problem summing an average in pivot table

Here's just 2016 data in a QVD and a sample application QVF with a straight table  Notice in the straight table I can get a daily average by warehouse, and get a sum of those averages in the Totals line.  I'd like to be able to get that same total in a pivot table too.

Thanks,

Mike

• ###### Re: Problem summing an average in pivot table

Hi Mike,

Using 2016 QVD I found that there are duplicate records

I used following script

NoConcatenate

CoolerOut2016:

OrdNumKey,

RecordType,

CaseNumber,

CaseKey,

OrderKey,

Warehouse,

WageLocationKey,

OrderType,

ReceiptType,

ItemNumber,

ShipToNumber,

ShipToCustomer,

SoldToNumber,

SoldToCustomer,

GroupNumber,

GroupCustomer,

PickDate,

PickFlag,

ItemOrderPallets,

CheckinHour,

Hour,

ShipHour,

UnitsPerHr,

CheckinTimestamp,

BackinTimestamp,

Date,

DayOfWeek,

ShipTimestamp,

TruckNumber,

ItemsOnOrder,

StagedItemsOnOrder,

CartonsOnOrder,

PalletsOnOrder,

AutoNumber(RowNo()) as RecNumber

FROM

CoolerOut2016.qvd

(qvd);

NoConcatenate

SAL_Warehouse_Info:

Date as GroupDate,

Count(RecNumber) as RecCountByDate

Resident CoolerOut2016

Group By Warehouse, Date

Order By Warehouse, Date;

AutoNumber(RowNo()) as RecNumber shows 1,296,935 records

If I don't use this field in the table and export data (I exported using selective warehouse to limit number of rows won't exceed Excel limit) then it gives me following. I've attached excels for your reference.

 Warehouse QVD Rows QlikTable Diff HOL 38483 vs 38405 78 HUR 68422 vs 68279 143 OXN 51601 vs 51486 115 SAL 634141 vs 631592 2549 SBF 55554 vs 55313 241 SSS 33318 vs 33093 225 VBF 9719 vs 9675 44 YSS 19926 vs 19809 117 YUM 385771 vs 384206 1565 1296935 1291858 5077

For warehouse SAL the UnitsLoaded Sum is 22,808,466, there are 297 different dates contributing to that sum. The Average comes to 76,796

I'm not sure why Qlik shows 1138 without any selection in Qlik. If I make selection of SAL then it shows 76,796 the formula I'm using is following

Can't understand this behavior and don't know what the reasoning for this can be

Also why some of the warehouses are not showing up as can be seen in the screen shot above

Can you please verify numbers, also come up with formulas in excel to get the numbers you have to arrive at. That will help in strategy we should take to come up with solution.

• ###### Re: Problem summing an average in pivot table

This is a typical problem of Grain Mis-match

Pitfalls of the Aggr function

Try this expression and see what you get (still might not be perfect just because averages Average – Which average? are sort of annoying)

• ###### Re: Problem summing an average in pivot table

Yes, that changed

• ###### Re: Problem summing an average in pivot table

Sunny,

Now how to get total 229,653 across all warehouses as second expression. That's sort of Mike's need, may not be exact but similar.

• ###### Re: Problem summing an average in pivot table

attachments

• ###### Re: Problem summing an average in pivot table

Can you post the qvw instead of excel or is it too big?

• ###### Re: Problem summing an average in pivot table

I'm attaching it. Due to slow connection it will take little time. It is 40+ MB. It will show up in another message once upload is complete.

• ###### Re: Problem summing an average in pivot table

Hi Sunny,

It is big but not as big as the excels. It is around 40+MB. Here it is.

• ###### Re: Problem summing an average in pivot table

For the sample attached... this would work

But, because there is a grain mis-match in the actual calculation, I have not been able to use this expression... as soon as I change to this kind of expression, the row-wise value change.

Not posting the qvw as it is huge... I just created the chart using these

Dimension

Warehouse

Expressions

• ###### Re: Problem summing an average in pivot table

Hi Sunny,

I think that should help Mike. Only thing I'm not sure about is that if it will work for him as we did it in QlikView and he is using QlikSense. I think it should be fine.

• ###### Re: Problem summing an average in pivot table

Expression will make be no different in QlikView or QlikSense and should work the same way

• ###### Re: Problem summing an average in pivot table

Sunny, thanks for collaborating with Vijay on this deep dive; I really appreciate it.  After reading about the pitfalls of Aggr I'm unclear as to how this would be a grain mismatch.  The expressions we've been using include Date, but don't reference Year, so the avg by Date should work.  Could there be a grain mismatch between Warehouse and PFWarehouse?  Since I only want results for specific warehouses, I'm using a Master dimension called PFWarehouse in my charts, which is defined as:

=If(Match(Warehouse,'HUR','SAL','SSS','SBF','YUM','YBF','YSS'),Warehouse,Null())

I'm then excluding Nulls from my charts and only showing the results with numbers.  However, in my expressions I am using Warehouse.  Perhaps this is causing bad behavior?

• ###### Re: Problem summing an average in pivot table

The grain mis-match is due to Warehouse not added within the Aggr() function, but it is part of your chart. That is the reason I asked you if you are confident that your row level numbers are correct or not... because as soon as I added Warehouse to the Aggr(), the row level number changed... but since you were so confident with the row level numbers that I was not able to make any further suggestions. But see if adding Warehouse to Aggr() in your actual app give more realistic numbers for you....

• ###### Re: Problem summing an average in pivot table

You are correct.  I've updated the daily average boxes formula to include Warehouse and it now evaluates correctly when comparing to specific dates/warehouses.

• ###### Re: Problem summing an average in pivot table

If this is correct, then this should work (I think)

• ###### Re: Problem summing an average in pivot table

That expression gives me an accurate total in the Totals line.  My goal is to be able to have a 'percent of total' column in the pivot table as well.  Normally this should be as easy as Column(1) / Column(2) or something similar, but since the total for all warehouses only shows in the Totals line I don't think that would work.

Is there a way to fine-tune your expression so that the total 204,926 shows up on each row?  I tried adding "TOTAL" in various places of the expression but that did not have the correct result.

Thanks!

• ###### Re: Problem summing an average in pivot table

• ###### Re: Problem summing an average in pivot table

YES!!!  That works perfectly.  Thank you!!!

• ###### Re: Problem summing an average in pivot table

Glad to see that finally you achieved closure on this question

• ###### Re: Problem summing an average in pivot table

Super

I tested this a while ago, but didn't give this because like I said, I thought your row numbers are the way you would like them to be....

• ###### Re: Problem summing an average in pivot table

I mistakenly thought my row numbers were correct..I appreciate that you stuck with me and figured it out.

• ###### Re: Problem summing an average in pivot table

Well, I almost lost hopes, it was Vijay who stayed with you Vijay. All kudos to him

• ###### Re: Problem summing an average in pivot table

Yes, much appreciate to vvira1316 also, who was very helpful and did an awesome deep dive into the issue.

Thanks to you both!

• ###### Re: Problem summing an average in pivot table

Thanks. I felt it was a genuine set analysis issue but at the same token when I saw the QlikSense file with numerous calculation, the data model I felt something about data issue and hence kept going towards raw data.

Always felt that Sunny's response in such situation are most of the time on target and I was surprised that why it was not working, that is when I took it on as a challenge to address it.

I'm glad that our effort paid off in finding right solution with right data as that is more crucial.

All the best.

BR,

Vijay

• ###### Re: Problem summing an average in pivot table

Second attachment

• ###### Re: Problem summing an average in pivot table

Thank you for doing the deep dive on this; I will comb through everything and let you know the results.  I'm surprised at the duplicate records since I used a SELECT DISTINCT in the original SQL that was used to create the QVD.  Nice collaboration between you and Sunny and I appreciate it very much.

• ###### Re: Problem summing an average in pivot table

By the way, it is possible for one CaseNumber to have multiple records, due to multiple item codes can be on each pallet.  So, perhaps they are not true duplicate records that you're looking at?