Budget vs. sales

[rberendsen]

Dear users,

I'm currently trying to match a budget with actual sales. However, I can't seem to figure out how to connect the data. In the attached file you'll find my datasource. First, I made a crosstable from the Budgetsheet (as seen in the code below). Now I would like to add another colum with the sales per ArtGroupCode per Salesman per Category, but only with the data from 2018. In the sheets InvoiceHeader and InvoiceLine, the orders are connected and specified with a date, range January 1st 2017 - March 9 2018. Any ideas on how to do this? I'm using QlikSense, pretty newbie and trying to learn 🙂 Thanks in advance!

Budget:
CrossTable(BudgetCategory, BudgetAmount, 2)
LOAD
SalesmanCode as BudgetSalesmanNo,
ArtGroupCode as BudgetArtGroupCode,
Overige,
Restaurant,
Supermarkt,
Toko
FROM [lib://***]
(ooxml, embedded labels, header is 1 lines, table is Budget);

[robert99]

Hi

I did a long reply but it disappeared when I went to post.

[robert99 · Accepted solution]

Ok try again. I will copy and paste in case it disappears again

There are various ways you can do this

Option 1 Use concatenate and summarise as required in a Chart (I would do it this way)
Script load budget
Budget:
CrossTable(Category, Amount, 5)
LOAD
'Budget' as Type ,
date(43101) as Date ,  //1/1/2018
year(43101) as Year ,   // year = 2018 or set up a calendar and link to this date
SalesmanCode as SalesmanNo,
ArtGroupCode as ArtGroupCode,
Overige,
Restaurant,
Supermarkt,
Toko
FROM [lib://***]
(ooxml, embedded labels, header is 1 lines, table is Budget);

SCRIPT LOAD ACTUAL

Actual:
concatenate (Budget)
LOAD
'Actual' as Type ,
Date ,
Year(Date) as Year ,   // or link date above to a calendar
SalesmanNo,
ArtGroupCode,

Category ,

Amount
FROM [

THEN LOAD SCRIPT AND go to a sheet

Set up a table or pivot table with columns required

Measures will be >>> 

Sum ({<Type = {Budget}>}Amount) 

Sum ({<Type = {Actual>}Amount) 

set up a filter pane and filter by Year = 2018

Or build into the set analysis

Sum ({<Type = {Budget}, Year = {2018} >}Amount) 

Sum ({<Type = {Actual}, Year = {2018} >}Amount) 

Alternatively you can sum in script

[robert99]

Alternative 2

Set up sum in script (I rarely do it his way. Only as a last resort0

So the budget as above

then load actual

Load actual as above but limit to 2018 (ensure this load doesn't concatenate with budget)

load

...

from ....

where year (Date) = 2018 ;

then  do sum

load

Type ,
SalesmanNo,
ArtGroupCode,

Category ,

Sum (Amount)

resident (Actual)

Group by Type, SalesmanNo, ArtgRoupCode , Category ;

then concatenate (or join) actual to budget and delete tables as required

[rberendsen]

Hi Robert,

Thanks for another try, this is almost working for me. I'm going to use your first posted alternative, that seems the most logical option. I also understand what you're doing, so that helps me understanding the scripting a bit more. However, I do not have an 'Actual' column in my datasource, so I'm stuck at the end of the "script load actual". Which sheet in my datasource do I have to select, or should I create some other table first to get to the actual sales? Thanks in advance for your reply.

[robert99]

Hi

If you went for alternative 2

Whenever you do sum you ahve to do it from data already loaded ins cript

So you need to create a temporary table Called Actual or ActualTemp (or whatever)

and then do a resident load on this table. Then when done delete the temporary table

But make sure ActualTemp does not concatenate with the budget table. Qlik will do it automatically if all field have the exact same name. Use NoConcatenate if required. Or just add in one extra row. Like 'temp' as Temp

[robert99]

Sorry you went for the first alternative

Use

'Actual' as Type

It not a field. It a label (added to every line) to use in set analysis

[rberendsen]

I feel a bit stupid, but I still can't get it to work. I got your explanation, but how do I create an "Actual"-table here as well, with the same fields (ArtGroupCode, SalesmanNo, Category)? I can't figure out how to combine the sheets from my datasource to create such a table

[robert99]

There are a number of article on joining tables

What I tend to often do is use mapping

So the main data table is Invoice lines. Use mapping load and apply map to load in the fields

• Date
• Customer
• SalesmanNo

But unsure how you get these two budget field from the actual data

Category

ArtGroupCode

You will need to somehow establish this link. By maybe loading in more dimension tables