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

Simple Chart - Forecast and Actual Paid does not work

 

Your help would be very much appreciated.  I have, what I think is, a simple setup with Budget line items (BLI), Statements of work (SOW, aka Purchase Order) and Transactions (Trxn, aka Paid).  The Bugdet includes, for each BLI, a monthly budget and forecast.  All the input is XLS.

 

 

When I create a QV table of sums by year, the yearly sum are not correct – the total is correct.

If I add a column to my Trxn table, TrxnYear, I can get the table above to work BUT if I add sum(TrxnAmount) to any other table, it has the same error.

 

 

Below is my data structure, the budget XLS and excerpts from a table box that lists the transactions.  I have included my MasterCalendar script also.   Below the structure are excerpts from a table box that lists the transactions demonstrate the issue…see Year in the last column (this is before I added TrxnYear).

 

 

 

Budget table (XLS)

 

 

When I ask to see Just 2016

 

 

When I ask to see just 2017

 

 

When I ask for ALL years (these dups don’t appear when I remove the Year column)

 

 

These dups don’t appear when I remove the Year column.

 

 

 

 

 

1 Solution

Accepted Solutions
nav_pienaar
Contributor II
Contributor II

Hi Karen

Try to get all the Facts in one fact table. The granularity could be different from for Actual transactions, budget & forecast. Having one fact table makes everything easier on the front end.

Hope this help you.

  Nav Pienaar

Notes.jpg

View solution in original post

15 Replies
vinieme12
Champion III
Champion III

Are you filtering on the correct year?

TrxnYear from Trxn table should be filtered on not Year from Master calendar .

I think your budget table has same blin numbers for multiple dates across both years

Create a composite key with a combination of blin and date to connect budget and Sow tables

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

vinieme12

I am using TrxnYear, I think.  I'll have to see.  how can I use a different year in the same table - as in BudgetYear for the Budget sum and TrxnYear for the Trxn sum?

Not applicable
Author

Are my answers in Canonical Calendar and/or HERE?

If so...having difficulty implementing a Canonical Calendar.

Not applicable
Author

Forgot to include my Master Calendar Script

MinMax:
LOAD
       
Min(BudgetDate) as MinDate,
       
Max(BudgetDate) as MaxDate
RESIDENT Budget;

LET vMinDate = Num(Peek('MinDate', 0,'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0,'MinMax'));

LET vToday = $(vMaxDate);

//*********Temporary Calendar*********
TempCal:
LOAD
       
date($(vMinDate) + rowno() - 1) As TempDate
AUTOGENERATE
       
$(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*********Master Calendar*********
MasterCalendar:
LOAD
       
TempDate As BudgetDate,
       
Week(TempDate) as Week,
       
Year(TempDate) as Year,
       
Month(TempDate) as Month,
       
Day(TempDate) as Day,
       
WeekDay(TempDate) as WeekDay,
        'Q' &
ceil(month(TempDate) / 3) as Quarter,
       
Date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
       
Week(TempDate)&'-'&Year(TempDate) as WeekYear,
       
inyeartodate(TempDate, $(vToday), 0) * -1 as CurYTDFlag,
       
inyeartodate(TempDate, $(vToday), -1) * -1 as LastYTDFlag
RESIDENT TempCal
ORDER BY TempDate ASC;

DROP TABLE TempCal;

 

 

vinieme12
Champion III
Champion III

Instead of connecting trxn to >> Sow to >> Budget

Change it to Sow >> Trxn >> to Budget

Applymap Blino to Trxn table using purchaseorderid , drop the field Blino from Sow and let it connect to Trxn only with purchaseorderid

In trxn table Create a key with Blino & TranDate as Key

In Budget table create a key with Blino & Budget Date as Key

Rename the Blino field in both tables, Trxn and Budget table to avoid a synthetic key

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

Even better would be to create a link table with a  master calendar

Sow >> Trxn >> Link table with keys from both tables + Calendar fields >> Budget table

Linking to two or more dates

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Not applicable
Author

I am going to try BLINo+Date  to add an key to the forecast and remove Forecast to a separate table with no date.  I don't want to add BLINo to the Trxn, as that would be only semi-automated.  The automated extract includes SOW, but no reference to the BLI.

BLI have monthly budget amount and monthly forecast amounts

BLIs can have multiple SOWs.  SOWs can have multiple transactions - sometimes more than one a month.

Not applicable
Author

I've worked thru a few options - learning now the mapping and link table work.  So now I think I understand what you are suggesting.  I will try that tomorrow.  And let you know!

nav_pienaar
Contributor II
Contributor II

Hi Karen

Try to get all the Facts in one fact table. The granularity could be different from for Actual transactions, budget & forecast. Having one fact table makes everything easier on the front end.

Hope this help you.

  Nav Pienaar

Notes.jpg