Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need to show individual monthly values instead of total values for Forecast field

Hi

I need to show Plan,Actual and Forecast values on monthly basis and their difference as a different fields.I got correct result for Plan and Actual as a totals but i am facing the issue  with forecast values.

Forecast value is getting total sum for months but i need as separate monthly values.

Please find the attached image for reference.

Sample.PNG

Expected Output: for the Forecast Field i want output like(Forecast is calculated value coming from above 1+11 forecast,2+10 Forecast .....11+1 forecast)

          ->   74 for Feb(which is correct from 1+11 Forecast)

          ->   74 for Mar(need to take value from 2+10 Forecast instead it is taking total of (1+11 forecast and 2+10 forecast))

          ->   74 for Apr(need to take value from 3+9 Forecast instead it is taking total of (1+11 forecast and 2+10 forecast and 3+9 forecast))) .....so on till Dec

Dimension and Expression is used:

Dimension:

Pick(Dim, Period,'Plan','Actual','Forecast','Actual-Plan','Forecast-Plan')

 

Expression:

=Pick(Dim, Sum(Value),

      Sum({<Period= {'Plan'}>}Value),

      Sum({<Period= {'Actual'}>}Value),

      Sum({<Forecast= {'Forecast'}>}Value),

      Sum({<Period= {'Actual'}>}Value)-Sum({<Period= {'Plan'}>}Value),

      Sum({<Forecast= {'Forecast'}>}Value)-Sum({<Period= {'Plan'}>}Value)

    

Thanks

1 Solution

Accepted Solutions
rubenmarin

Hi, Is the period part of the final table? and you want the bottom value for each month? Maybe with:

Top(Sum(Value), RangeMax(ColumnNo()-1, 1))

View solution in original post

1 Reply
rubenmarin

Hi, Is the period part of the final table? and you want the bottom value for each month? Maybe with:

Top(Sum(Value), RangeMax(ColumnNo()-1, 1))