Comparing Longitudinal Data with Cyclics

[bobswidarski]

The goal is to create a straight table which includes a cyclic that displays the total difference across all selected Fiscal Months in units between two the forecasts.  

Background:

Many forecasts (planning books) are loaded into the application for comparison.

Intially the user selects two periods to compare (primary and secondary).

The challenge: We had to stitch two values together, actuals + forecast, to get a true comparison between the forecasts (planning books).

The fields:

[APO Final Forecast] -> is the final forecast for a product by month for a given planning book

[APO Order Qty] -> is the actual sales

So the idea to get actuals + forecast is for when, relative to the selected planning book, a month is in the past use the Actuals otherwise use the planning book.

This was helpful but didn't solve.

https://community.qlik.com/t5/QlikView-App-Development/Aggr-Function-using-Cyclic-Groups/m-p/158354#...

This code works in a straight table without cyclic:

sum( 
 aggr(
if(SUM({$<PlanPeriod={$(vSecondarySelected)}>}[APO Final Forecast])>0,SUM({$<PlanPeriod={$(vSecondarySelected)}>}[APO Final Forecast]),SUM({$<PlanPeriod={$(vPlanningPeriodCur)}>}([APO Order Qty])))
- if(SUM({$<PlanPeriod={$(vPrimarySelected)}>}[APO Final Forecast])>0,SUM({$<PlanPeriod={$(vPrimarySelected)}>}[APO Final Forecast]),SUM({$<PlanPeriod={$(vPlanningPeriodCur)}>}([APO Order Qty])))
      ,[Fiscal Month]))

This is what I tried with the cyclic and it didn't work

sum( 
 aggr(
if(SUM({$<PlanPeriod={$(vSecondarySelected)}>}[APO Final Forecast])>0,SUM({$<PlanPeriod={$(vSecondarySelected)}>}[APO Final Forecast]),SUM({$<PlanPeriod={$(vPlanningPeriodCur)}>}([APO Order Qty])))
- if(SUM({$<PlanPeriod={$(vPrimarySelected)}>}[APO Final Forecast])>0,SUM({$<PlanPeriod={$(vPrimarySelected)}>}[APO Final Forecast]),SUM({$<PlanPeriod={$(vPlanningPeriodCur)}>}([APO Order Qty])))
       ,  $(=GetCurrentField([Summary Cyclic])) ,[Fiscal Month]    ????   
      )
      )

The cyclic has dimensions like Forecast Material, Product Line, and Product Type

[bobswidarski]

Internal support got me the answer. The answer was in the code structure and set analysis.  This was no t a cyclic issue in the end.

https://www.youtube.com/watch?v=YMQJnKMkfxg

Changes that helped:

1.   Changing from SUM(AGGR(SUM())) structure to one sum.

2.  Adding a condition for [Fiscal Month]  which basically replaced the if(sum()) statement

3.  use of *=  (star equals) to respect selections

4.  a snippet of code the converts Planning Book names to the same format as [Fiscal Month]

Final code that works:

(
SUM({$<PlanPeriod={$(vSecondarySelected)},[Fiscal Month]*={">=$(=date(date#(capitalize(vSecondarySelected),'MMMYY'),'YYYYMM'))"}>}[APO Final Forecast])
+
SUM({$<PlanPeriod={$(vPlanningPeriodCur)},[Fiscal Month]*={"<$(=date(date#(capitalize(vSecondarySelected),'MMMYY'),'YYYYMM'))"}>}[APO Order Qty])
)
-
(
SUM({$<PlanPeriod={$(vPrimarySelected)},[Fiscal Month]*={">=$(=date(date#(capitalize(vPrimarySelected),'MMMYY'),'YYYYMM'))"}>}[APO Final Forecast])
+
SUM({$<PlanPeriod={$(vPlanningPeriodCur)},[Fiscal Month]*={"<$(=date(date#(capitalize(vPrimarySelected),'MMMYY'),'YYYYMM'))"}>}[APO Order Qty])
)