Ignore dimension for budget

[hildacgg]

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...

[sunny_talwar]

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

[rammarthi]

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

[Colin-Albert]

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.

[hildacgg]

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.

[zebhashmi]

Not sure but try to charge this

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

to

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

Your cross table is transfromed?

[Colin-Albert]

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.

[Colin-Albert]

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

Budget table with different granularity

[hildacgg]

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