4 Replies Latest reply: May 8, 2018 11:11 PM by Steven van der Walt RSS

    Aggregation Issue

    Steven van der Walt

      Hi All,

       

      I am new to QlikSense world and hope you can help me.

       

      I am trying to create a Rolling Forecast Measure.  I have the following data:

       

      customerresourceyearmonthweeknoversionvalueNew Column (Rolling FC Value)
      AX12018Apr201804 W1Actual850850
      AX12018Apr201804 W1Forecast10000
      AX12018Apr201804 W2Actual780780
      AX12018Apr201804 W2Forecast10000
      BY12018Apr201804 W3Forecast10001000
      BX12018Apr201804 W4Forecast10001000
      AY12018May201805 W1Forecast10001000
      AX12018May201805 W2Forecast10001000

       

      I created the following Variable:

           Name = currentWeekno

           Definition = 201804 W2

       

      and created the following Measure:

           Name = Rolling FC Value

           Expresion:

           if(weekno<=$(currentWeekno), Sum(if(version='Actual',value)), if(weekno>$(currentWeekno), Sum(if(version='Forecast',value)),0))

       

      This expression works well, but it only aggregate for the "Weekno" dimension.

       

      I need this to aggregate for all other dimensions as well i.e "Year", "Month", "Customer" and "Resource".

       

      Any help would be appreciated.

        • Re: Aggregation Issue
          Toni Kautto

          Could you clarify the table setup by providing a simple sample app?

           

          It would be good to understand which columns are dimensions and measures in this example. Please also confirm what results you expect in the table when the measure is aggregated according to your expectations.

            • Re: Aggregation Issue
              Steven van der Walt

              Hi Toni,

               

              I have uploaded a Sample.qvf file and shared it with you.

               

              In the above example there is only one measure in the table i.e value.

               

              The new colomn (Rolling FC Value) is a new measure that 'i've created in my Master Items.  The results should be the either the forecast value or the actual value from the value column - based on the currentWeekno variable that I've created.

                • Re: Aggregation Issue
                  Toni Kautto

                  I'm still not sure exactly what numbers you expect to see when the measure if working as you expect. If you can clarify the expected result it would be easier to validate an accurate solution.

                   

                  The version field and weekno<=$(currentWeekno) does not indicate the same flag. Does that mean you want to aggregate the forecast based on the comparison?

                  The following expression will give you Sum(value) for records with forecast flag that are still forecast according to your variable;

                  Sum({<version={"Forecast"},weekno={"<=$(currentWeekno)"}>} value)


                  The following expression will give you Sum(value) for records with actual flag based on your variable;

                  Sum({<version={"Actual"},weekno={"<=$(currentWeekno)"}>} value) +

                  Sum({<version={"Forecast"},weekno={">$(currentWeekno)"}>} value)