topic Simple Chart - Forecast and Actual Paid does not work in QlikView

Simple Chart - Forecast and Actual Paid does not work

Fri, 10 Feb 2017 17:57:35 GMT

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.

Re: Simple Chart - Forecast and Actual Paid does not work

vinieme12 — Fri, 10 Feb 2017 19:38:42 GMT

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

Re: Simple Chart - Forecast and Actual Paid does not work

Fri, 10 Feb 2017 20:37:43 GMT

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?

Re: Simple Chart - Forecast and Actual Paid does not work

Fri, 10 Feb 2017 20:39:35 GMT

Are my answers in Canonical Calendar and/or HERE?

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

Re: Simple Chart - Forecast and Actual Paid does not work

Fri, 10 Feb 2017 21:36:34 GMT

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;

Re: Simple Chart - Forecast and Actual Paid does not work

vinieme12 — Sat, 11 Feb 2017 06:14:44 GMT

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

Re: Simple Chart - Forecast and Actual Paid does not work

vinieme12 — Sat, 11 Feb 2017 06:21:53 GMT

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

Re: Simple Chart - Forecast and Actual Paid does not work

Mon, 13 Feb 2017 15:50:40 GMT

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.

Re: Simple Chart - Forecast and Actual Paid does not work

Mon, 13 Feb 2017 18:15:15 GMT

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!

Re: Simple Chart - Forecast and Actual Paid does not work

nav_pienaar — Wed, 22 Feb 2017 15:11:17 GMT

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

Re: Simple Chart - Forecast and Actual Paid does not work

Wed, 22 Feb 2017 18:49:36 GMT

I have made progress. What STILL DOESTN" WORK is at the bottom of this entry.

WHAT WORKS:

I have gotten the DateBridge to work (from ). I added the "TKey" manually to my files as I couldn't get ApplyMap to work:

My transaction now sum correctly. And I have a lovely Budget, Foreccast and Spend to date (Paid) chart.

WHAT DOESN'T WORK

However - I lost the association between transaction and Workstream. I have not been able to find a way to get it without generating a Loop or SynKey.

Here's my data model currently:

Re: Simple Chart - Forecast and Actual Paid does not work

Wed, 22 Feb 2017 21:01:16 GMT

Nav,

Would that one fact table be created manually or out side of Qlikview? That would be a onerous task. I get 100's of transactions a month.

But it could be done if all else fails.

- Karen

Re: Simple Chart - Forecast and Actual Paid does not work

Wed, 22 Feb 2017 21:01:58 GMT

I tried combining BLI and Budget in the same table. That didn't help.

- Karen

Re: Simple Chart - Forecast and Actual Paid does not work

nav_pienaar — Wed, 22 Feb 2017 21:10:38 GMT

No, you will have to do it as part of transformation process in QlikView.

The best way to test your own logic and to get help on it if it doesn't work, is to build a small sandbox app with just the key fields and a hand full of record.

Nav Pienaar

Re: Simple Chart - Forecast and Actual Paid does not work

Thu, 16 Mar 2017 18:16:12 GMT

nav.pienaar,

Your answer worked mostly. I did not need to create one fact table. With or without that the problems were the same. the only way I could get the association back that I needed was to live with a syn key. I had a developer in our office try and she could not find another way.

Re: Simple Chart - Forecast and Actual Paid does not work

Thu, 16 Mar 2017 18:19:33 GMT

Final working table scheme