Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
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

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)

View solution in original post

4 Replies
Highlighted
Employee
Employee

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.

Highlighted
Contributor II
Contributor II

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

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)

View solution in original post

Highlighted
Contributor II
Contributor II

Hi Toni,

Many thanks!

The second formula you provided is working perfectly.

Much appreciated.