Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
rberendsen
New Contributor II

Budget vs. sales

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);

Tags (2)
1 Solution

Accepted Solutions
robert99
Valued Contributor II

Re: Budget vs. sales

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

 

 

 

 

 

 

10 Replies
robert99
Valued Contributor II

Re: Budget vs. sales

 
robert99
Valued Contributor II

Re: Budget vs. sales

Hi

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

robert99
Valued Contributor II

Re: Budget vs. sales

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
Valued Contributor II

Re: Budget vs. sales

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
New Contributor II

Re: Budget vs. sales

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
Valued Contributor II

Re: Budget vs. sales

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
Valued Contributor II

Re: Budget vs. sales

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
New Contributor II

Re: Budget vs. sales

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
Valued Contributor II

Re: Budget vs. sales

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