Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
Not sure I understand the use of your calculated dimension?? Would you be able to share a qvw sample to look at this?
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
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.
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.
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?
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.
Have a look at this thread showing how to handle sales & budgets with different levels of granularity
It will create a Syntetic key with my calendar because my key is the Date itself like 1/1/18