Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

steven_vdwalt
New Contributor II

Aggregation Issue

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.

1 Solution

Accepted Solutions
Employee
Employee

Re: Aggregation Issue

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)

4 Replies
Employee
Employee

Re: Aggregation Issue

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.

steven_vdwalt
New Contributor II

Re: Aggregation Issue

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.

Employee
Employee

Re: Aggregation Issue

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)

steven_vdwalt
New Contributor II

Re: Aggregation Issue

Hi Toni,

Many thanks!

The second formula you provided is working perfectly.

Much appreciated.

Community Browser