Aggregation Issue

[Anonymous]

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:

customer	resource	year	month	weekno	version	value	New Column (Rolling FC Value)
A	X1	2018	Apr	201804 W1	Actual	850	850
A	X1	2018	Apr	201804 W1	Forecast	1000	0
A	X1	2018	Apr	201804 W2	Actual	780	780
A	X1	2018	Apr	201804 W2	Forecast	1000	0
B	Y1	2018	Apr	201804 W3	Forecast	1000	1000
B	X1	2018	Apr	201804 W4	Forecast	1000	1000
A	Y1	2018	May	201805 W1	Forecast	1000	1000
A	X1	2018	May	201805 W2	Forecast	1000	1000

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.

[ToniKautto]

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.

[Anonymous]

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.

[ToniKautto · Accepted solution]

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)

[Anonymous]

Hi Toni,

Many thanks!

The second formula you provided is working perfectly.

Much appreciated.