4 Replies Latest reply on Dec 5, 2016 12:02 PM by Stephen Rizzo

    Cannot Mixed aggregations  -

    Rob Freyne

      Hi, I have a problem that I can see lots of people have had previously. I have tried unsuccessfully to try and get a suitable solution.


      I’m trying to compare 'Distribution' (calculated field ‘in Stock / Number of records’) of the last two weeks vs previous two weeks and then the % difference.


      Distribution is when the test is carried out is in stock 100% distribution or tested and out of stock 0% at the SKU level. This is when an 'SM' calls to a location, they record whether the item is in stock or not. (In stock = 1 in stock, 0=out of stock).


      Previously we would tag/ hardcode the 'last two weeks calls' in the data source and then use an ’if statement’ to ascertain what records were applicable and that was working fine.




           if [Last Two Weeks]="Last Two Weeks"    THEN   ([In Stock]) end)


           SUM(if [Last Two Weeks]="Last Two Weeks" THEN ([number of records])




      However, I am attempting to use a relative or anchor date now and this is where the problem arises.


      I can get it to give me a correct answer for last two week and previous using a formula with no aggregation and then average but then I can’t subtract two averages to give me the difference. this obviously is not ideal


      Distribution no Agg  => [In Stock]/[Number of Records]

      IF (

          [Actual Call Date] <=  [Anchor Date]

              AND (DATEDIFF('day',[Actual Call Date],[Anchor Date])<= 14)

                  AND (DATEDIFF('day',[Actual Call Date],[Anchor Date])> 0)

          ) THEN  [Distribution no Agg]


      To further complicate things further I have a 'change view' parameter so I’m not sure whether the LOD route is suitable. Can somebody help me understand the level of details in this scenario? for some reason, I think its something to do with callid but I am obviously not understanding the scenario correctly. I I have included a workbook any assistance would be very appreciative.


      I understand that there is an aggregation somewhere that I can't see maybe its the way the data is laid out as each call as a unique id where they may or may not test an individual SKU but that's a guess. I would really appreciat asoe help with this please as i have been going round in circles for the last couple of days,


      Message was edited by: Rob Freyne

        • 1. Re: Cannot Mixed aggregations  -
          Stephen Rizzo

          Could you post the .twbx file? .twb files do not include the data.


          Your post was a bit hard to follow, but if I understand you correctly, you want the percentage of items that are in stock in the two week period preceding some predetermined date. If so, would the following calculated field work?


          [Percentage in Stock] : AVG(IF [Actual Call Date] >= DATEADD('day', -14, [Anchor Date]) AND [Actual Call Date] < [Anchor Date] THEN [In Stock] END)


          The result could then be formatted as a percentage.

          • 2. Re: Cannot Mixed aggregations  -
            jon rios

            hi Rob, you need to post a TWBX workbook not a TWB... save as "packaged workbook"

            • 3. Re: Cannot Mixed aggregations  -
              Rob Freyne

              Firstly thanks everybody for your help sorry should have had the data as well. Also apologised or not giving a prompt response.   I was away for the weekend so had no computer.

              Ok down to work. Yes, Stephen, I think you have summed it up pretty well. I want to be able and dice distribution using the many fields such as SM SKU , Store Name group etc. I have made your changes and they are in the tab veriosn 2 but again as you can see there are some inaccuracies although they are minimal. I don't think there would be aproblemns in this pcase particularly as this is jsuty one of mynay KPI'S.

              then end result will enable to determine the anchor date and determine what period whether it be last three months 26 vs 26,  year on year etc.

              • 4. Re: Cannot Mixed aggregations  -
                Stephen Rizzo

                We're glad to help! By the way, the reason your two fields ([Last 2 wks % in Stock 2] and [Previous 2 wks % in Stock]) don't match is because the first calculates the % in stock for the last 2 weeks, whereas the second calculates the % in stock for the last 4 weeks. You can make the period length variable by replacing the 14 in the formula with a integer parameter.