Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Facts vs Budget - Budget not available in Future

Hello experts,

I’ve got facts data (sales) and budget data.

The facts (sales) of 2015 are available from January untill December.

The facts (sales) of 2016 are available from January untill the current month, which is April.

The budget data is available for 2016 from January untill December.

Screenshot02.png

The requirement:

The customer wants to see the sales 2015, sales 2016 and budget 2016 in the same table.

My issue is, that I cannot view the budget data of 2016 in the table, which lays in future – after April (red).

Screenshot01.png

It is because of the link between the facts & budget data. I just cannot figure it out how to solve this issue.

I want to see ALL BUDGET data of 2016 ...

All relevant files, to play with, are attached.

Does anyone have any idea for this “standard” requirement?

Regards

Nik

Tags (2)
1 Solution

Accepted Solutions
stabben23
Honored Contributor

Re: Facts vs Budget - Budget not available in Future

Try to do it like this :

SalesDataTab:
LOAD
Area & Team & Year & Month as %Key_AreaTeamYearMonth,
Area ,
Team ,
CustomerID ,
CustomerName ,
Category ,
Product ,
Year ,
Month ,
Year & Month  as YearMonth,
Sales as SalesData_Sales,
Profit as SalesData_Profit,
'Sales'
as Table
FROM
Tab_SalesData.xlsx
(
ooxml, embedded labels, table is Tab1);


Concatenate
BudgetDataTab:
LOAD
Area & Team & Year & Month as %Key_AreaTeamYearMonth,
Area ,
Team ,
Year ,
Month ,
Year & Month  as YearMonth,
BudgetSales as BudgetData_BudgetSales,
'Budget'
as Table
FROM
Tab_BudgetData.xlsx
(
ooxml, embedded labels, table is Tab1);

Concatenate is standard as mention above.

6 Replies

Re: Facts vs Budget - Budget not available in Future

What if you leave the two tables disconnected?

Capture.PNG

Use this expression for Budget amount:

Aggr(If(SalesData_Month = BudgetData_Month, (SUM({<SalesData_Year = {"$(=$(v_YCurr))"}>} BudgetData_BudgetSales))), BudgetData_Month, SalesData_Month)

Capture.PNG

alexpanjhc
Valued Contributor

Re: Facts vs Budget - Budget not available in Future

I would concat the sales and budget tables--- i think that is the "standard" way to do it.

stabben23
Honored Contributor

Re: Facts vs Budget - Budget not available in Future

Try to do it like this :

SalesDataTab:
LOAD
Area & Team & Year & Month as %Key_AreaTeamYearMonth,
Area ,
Team ,
CustomerID ,
CustomerName ,
Category ,
Product ,
Year ,
Month ,
Year & Month  as YearMonth,
Sales as SalesData_Sales,
Profit as SalesData_Profit,
'Sales'
as Table
FROM
Tab_SalesData.xlsx
(
ooxml, embedded labels, table is Tab1);


Concatenate
BudgetDataTab:
LOAD
Area & Team & Year & Month as %Key_AreaTeamYearMonth,
Area ,
Team ,
Year ,
Month ,
Year & Month  as YearMonth,
BudgetSales as BudgetData_BudgetSales,
'Budget'
as Table
FROM
Tab_BudgetData.xlsx
(
ooxml, embedded labels, table is Tab1);

Concatenate is standard as mention above.

Re: Facts vs Budget - Budget not available in Future

Or create a link table containing four dimension fields (Team, Area, Month, Year) and two keys: one that connects to the Facts table, and another one that connects to the Budget table. The key values are composed of the values in the other dimension fields, like

:

Area & '|' & Team & '|' & Year & '|' & Month AS BudgetKey,

Area & '|' & Team & '|' & Year & '|' & Month AS ActualsKey,

:


Make sure that all future periods are listed in this Link table as well. A cartesian JOIN of your PeriodCalendar to all Area & Team values will do the job.

Not applicable

Re: Facts vs Budget - Budget not available in Future

I would say: Sunny 4 president ; )

Your solution does exactly the trick!!!

It seems a bit strange to me, still ; )

I'm going to check out other sugestions, too. But for anyone who might read this post having the similar issue - sunny's idea works. I do not see any issue, which might occur by using the provided solution

@ all experts: do you believe it might cause some issues or is it really just THAT simple

Not applicable

Re: Facts vs Budget - Budget not available in Future

Hi Alex,

your solution did work, too.

I've uploaded the ready to use file for the community.