topic Ignore dimension for budget in QlikView

Ignore dimension for budget

hildacgg — Wed, 25 Nov 2020 16:16:04 GMT

I have sales, backlog and booking data which each record contains a date, product number and amount. and I have a quarterly budget which it doesn't have any date associated with. I am trying to create a chart by month but the budget data is not calculating.

Here is my table:

Month	Bookings $	Backlog $	Sales	Budget	RowNo()
1	38,501	1,000	175,000	0	1
2	5,423	2,100	6,000	0	2
3	8,456	3,000	100	0	3
4	0	5,000	0	0	4
5	0	4,000	0	0	5
6	0	300	0	0	6
7	0	200	0	0	7
8	0	100	0	0	8
9	0	50	0	0	9
10	0	100	0	0	10
11	0	100	0	0	11
12	0	0	0	0	12

Budget Data (Sample):

Product Nbr	Q1	Q2	Q3	Q4
A	100	110	141	177.1
B	200	220	282	354.2
C	50	55	70.5	88.55
B	500	550	705	885.5
E	100	110	141	177.1

I am using a calculated dimension: Month:

=if(ColumnNo()=5,RowNo(),Month)

and for my budget column (Column 5) I have tried several expressions:

One:

sum({<Month=>}if(RowNo() = 1 or RowNo()= 2 or RowNo = 3,Q1/3))

Two:

if(RowNo() = 1 or RowNo()=2 or RowNo = 3,sum({<Month=>}Q1 /3))

But still it doesn't show any numbers. Please help...

Re: Ignore dimension for budget

sunny_talwar — Wed, 28 Feb 2018 15:54:33 GMT

Not sure I understand the use of your calculated dimension?? Would you be able to share a qvw sample to look at this?

Re: Ignore dimension for budget

rammarthi — Wed, 28 Feb 2018 16:01:11 GMT

First read the Budget data as below using crosstable function and resident with preceding load:

Prduct Nbr, Quarter, QtrStrtDate, QtrEndDate, Budget

Use interval match to match date from (Here is my table:) between QtrStartDate and QtrEndDate. Now you will get a resultant table like this

Prduct Nbr, Quarter, QtrStrtDate, QtrEndDate, Budget, Bookings, Backlog, Sales.

Regards

Ram

Re: Ignore dimension for budget

Colin-Albert — Wed, 28 Feb 2018 16:03:22 GMT

Hi Hilda,

Your budget is Quarterly, so the budget could be assigned to the first or last date in the quarter, or you could apportion 1/3 of the Quarter bufget to each month in the budget. Then your monthly sales will have a budget for comparison.

I would do this by concatenating the budget data to your sales data with a new column for the budget value.

If you post an example QVW this would help.

Re: Ignore dimension for budget

hildacgg — Wed, 28 Feb 2018 16:11:47 GMT

I could only assign one date to the budget:

Product Nbr	Q1	Q2	Q3	Q4	Date
A	100	110	141	177.1	1/15/2018
B	200	220	282	354.2	1/15/2018
C	50	55	70.5	88.55	1/15/2018
B	500	550	705	885.5	1/15/2018
E	100	110	141	177.1	1/15/2018

This will assign everything to the first quarter, first month... I can NOT assign several dates to each record.

This is sample data but I have hundreds of columns and millions of records a cross table will not be effective.

Re: Ignore dimension for budget

zebhashmi — Wed, 28 Feb 2018 16:18:47 GMT

Not sure but try to charge this

if(RowNo() = 1 or RowNo()=2 or RowNo = 3,sum({<Month=>}Q1 /3))

Sum({<RowNo() ={1,2,3}>}yourField)

Your cross table is transfromed?

Re: Ignore dimension for budget

Colin-Albert — Wed, 28 Feb 2018 16:20:43 GMT

Hi Hilda,

You need to use the crosstable function to unpivot your budget data so you get something like

Product Qtr Budget

A Q1 100

A Q2 110

A Q3 141

A Q4 177.1

B Q1 200

B Q2 220

etc

Then you can apportion the quarter values into months by dividing by 3.

This assumes that the Quarterly budget is linearly apportioned to each month in the quarter.

Also for completeness you should include a Year field in your sales & budget data.

Re: Ignore dimension for budget

Colin-Albert — Wed, 28 Feb 2018 16:27:39 GMT

Have a look at this thread showing how to handle sales & budgets with different levels of granularity

Budget table with different granularity

Re: Ignore dimension for budget

hildacgg — Wed, 28 Feb 2018 16:39:15 GMT

It will create a Syntetic key with my calendar because my key is the Date itself like 1/1/18