Interesting aggregate challenge

[markobanjanin]

Hello Community,

I am faced with an interesting challenge - I need to perform an aggregations against my data set across multiple years and show counts that include/exclude specific records.

Let us assume I am working at an ice cream shop and on yearly basis i am capturing the following data for ice cream production:

Type:  Actual/Forecast - denotes what I am either planning to use as my ingredients vs what I actually used

Ingredient: ice cream ingredient

I am reporting on yearly forecasts and actuals twice in a calendar year.

Example data set:

Year	Type	Product	Ingredient
2020	Actual	Ice cream	Water
2020	Actual	Ice cream	Sugar
2020	Actual	Ice cream	Food color
2020	Forecast	Ice cream	Water
2020	Forecast	Ice cream	Sugar
2020	Forecast	Ice cream	Food color
2020	Forecast	Ice cream	Egg
2019	Actual	Ice cream	Water
2019	Actual	Ice cream	Sugar
2019	Actual	Ice cream	Food color
2019	Actual	Ice cream	Milk
2018	Actual	Ice cream	Water
2018	Actual	Ice cream	Egg
2018	Actual	Ice cream	Milk
2018	Actual	Ice cream	Almod milk
2019	Forecast	Ice cream	Food color
2019	Forecast	Ice cream	Water
2017	Actual	Ice cream	Water
2017	Actual	Ice cream	Flour

Expected outcome:

year	actual ingredient count	need to show
2020	3	6
2019	3	4

Logic for Need to show calculation:

For year 2020 I want to include all Actual and Forecast ingredients and on top of that add Actual ingredients from 2019 and 2018. Year 2020 Actuals are my basis (there are 3 ingredients) . Forecast 2020 includes 1 new ingredient (Eggs), Actuals 2019 and 2018 include 2 more ingredients (Milk + Almond milk). Same ingredients should not be double counted, only different one.

The same logic applies when I switch to year 2019; I would use 2019 Actuals as the basis and compare the list to Forecast 2019 and Actuals 2018 and 2017.

What would be the best way to tackle this? 

[markobanjanin]

@Gysbert_Wassenaar maybe I can pick your brain here? 🙂

[Gysbert_Wassenaar]

I think I must be missing part of the logic. For 2019 I get 4 Actual ingredients and 7 need-to-show:

Actual 2019: Water, Sugar,Food color, Milk: 4

Forecast 2019: Food color, Water: 0 because they're also in the Actual 2019

Actual 2018: Egg, Almond milk: 2

Actual 2017: Flour: 1

Total 7

[markobanjanin]

Thank you!

You understood it well. Mistake was on my end. I started developing loops so once i saw this i was felt i started overengineering the solution. What's the purpose of the Offset column? Incorporates years that need to be included in the aggregation

[Qlik1_User1]

@markobanjanin  can you please share the expression used for this solution.

[markobanjanin]

@Gysbert_Wassenaar, i added a few more data points to the table and noticed Offset 0 included both Actuals and Forecast for each AsofYear. I added a more detailed grain to the AsOfYear2 table and joined the two with a new key. 

[markobanjanin]

@Qlik1_User1 you can see used approach in attached qvf (1) file. Initially I was building this in the script but seems it was overengineering it. Now i just need to automate mapping creation

[Gysbert_Wassenaar]

Hi Marko,

Yes, the Offset is to make sure that only the forecast of the Year with the same value as AsOfYear is included, ie the Year that has Offset value 0. The Actuals are aggregated for all three Years per AsOfYear.