Comparing Longitudinal Data with Cyclics

bobswidarski — Sat, 16 Nov 2024 04:07:34 GMT

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#M33464

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

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

Re: Comparing Longitudinal Data with Cyclics | Set proper conditions for set analysis

bobswidarski — Thu, 14 Nov 2019 21:40:14 GMT

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]) )

topic Comparing Longitudinal Data with Cyclics in App Development

Comparing Longitudinal Data with Cyclics

Re: Comparing Longitudinal Data with Cyclics | Set proper conditions for set analysis