Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Stuck: How to design a monthly Actual and Forecast table like this in QlikView user interface?

Hello,

Our money department has asked for a table like this:

  

ActualActualActualActualActualActualForecastForecastForecastForecastForecastForecast
Month1Month2Month3Month4Month5Month6Month7Month8Month9Month10Month11Month12Total
Turnover
Profit
Staff Costs
Office Costs
Total

The logic is:

Show the actual costs for the previous month, any months after show the Forecast values.

I have the necessary fields such as:

Field identifying Actual or Budget

Month1, Month2 etc are in Master Calendar

Turnover etc values are also available.

But I am stuck and not sure actually how to design the above kind of table in the user interface.

Could anyone please share an example?

6 Replies
Highlighted
Contributor III
Contributor III

I would suggest trying to get your users to move away from straight tables like what you're showing here. It's easier to spot trends in a quick bar chart. I have a screen shot below (from Qlik Sense) where I am showing year-to-date sales against prior year sales with forecast only showing from previous month forward. Each has a separate expression and the forecast data I am only bringing in the previous month forward, so there are not any historical values. If you have to have historical values, then I would suggest using set analysis in your expression to only show future months.

2018-03-08_151024.png

Highlighted
Creator II
Creator II

To complete the chart, you will need to do below.

There are two dimensions

     One for Month field

     Another one to show either 'Actual/Forecast' (You need a condition to show actual or not based on current date)

There are 4 (Actual + Forecast) expressions for below 4 rows

   Turnover

   Profit

   Staff Costs

   Office Costs

Highlighted
Creator III
Creator III

Hi there and everyone reading this,

Thanks for tying to help.

Fields I have:

I have the Month field:

Values are Month1, Month2 etc

The Months run from April to March, so in a real world example we would currently be in Month12.

For the Actual / Forecast, currently these two values reside in a "FigureType" field where the values are "Forecast" or Actual".

Logic I want to build:

Show Actual numbers for all the months before the current month, else for all the months from the current month, show the Forecast values.

So in a real world exaple it would be show me the Actual values for all the months Month11 to Month1, and show Forecast for Month12.

How can I do this?

I am working with accountants and they only want to see this in the original table format like my original post.

The data is sensitive so unfortunately I cannot share a sample here.

Highlighted
Contributor III
Contributor III

In your script you can copy the forecast and actual numbers into separate columns. In your pivot table create an expression for actual and another expression for forecast. Turn off the option to show zero values. That may give you what you need.

Creator II
Creator II

Please find the attached sample qvw.

Capture.PNG

Hopefully this helps you.

Highlighted
Creator II
Creator II

I created another sample which meets your requirement more.

Capture.2PNG.PNG

Hopefully you like it.