Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
I have a table in this format
Header 1 | Header 2 | Header 3 | Header 4 | Header 5 | Header 6 | Header 7 | Header 8 | Header 9 |
---|---|---|---|---|---|---|---|---|
Particulers | Budget | Actual | ||||||
Net Revenue | 15000 | 16000 | ||||||
Selling exps | 20000 | 22000 | ||||||
Net realisation | 67000 | 6543 | ||||||
other | 5432 | 9876 | ||||||
expection | 10000 | 11000 | ||||||
gain | 7896 | 8976 |
We have a requirement to see the report in below Structure.For Current Fiscal year 2015-16..Please Check and help me on this..
Header 1 | Header 2 | Header 3 | Header 4 | Header 5 | Header 6 | Header 7 | Header 8 | Header 9 | Header 10 | Header 11 | Header 12 | Header 13 | Header 14 | Header 15 | Header 16 | Header 17 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Pariculers | Apr | May | Jun | Q1 | Jul | Aug | Sep | Q2 | Oct | Nov | Dec | Q3 | Jan | Feb | Mar | Q4 |
Net Revenue | 1500 | 1600 | 1700 | 4800 | 1200 | 1200 | 1200 | 3600 | 1100 | 1100 | 1100 | 3300 | 1300 | 1300 | 1300 | 3900 |
Same For Selling Exps,Net Realisation ,Other, Expection, Gain
Hi,
can you please explain where the month and quarter related information is coming from?
thanks
regards
Marco
I think you need to explain a little more detailed how you come from your Budget and Actual Net Revenue values to the Net Revenue values in your requested result table.
Can't see any rule how to distribute the values (and the sum of requested result revenue values don't seem to match the input record).
I think this helps you,
Loading Script:
t1:
LOAD * INLINE [
Type, Date, Amount
Net Revenue, 4/4/2015, 100
Net Revenue, 5/4/2015, 100
Net Revenue, 6/4/2015, 100
Net Revenue, 7/4/2015, 400
Net Revenue, 8/4/2015, 500
Net Revenue, 9/4/2015, 600
Net Revenue, 10/4/2015, 700
Net Revenue, 11/4/2015, 800
Net Revenue, 12/4/2015, 900
Net Revenue, 1/4/2015, 100
Net Revenue, 2/4/2015, 200
Net Revenue, 3/4/2015, 200
Net Revenue, 5/4/2015, 300
Net Revenue, 6/4/2015, 500
Net Revenue, 4/4/2015, 100
];
t2:
load Type, Date,
pick(num#(mid(Date,1,index(Date,'/')-1)),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as Month,
pick(num#(mid(Date,1,index(Date,'/')-1)),'Q4','Q4','Q4','Q1','Q1','Q1','Q2','Q2','Q2','Q3','Q3','Q3') as Quarter,
Amount
resident t1;
drop table t1;
Create pivot chart using dimension Type, Month, Quarter with expression as sum(Amount)
Show Partial Sums should be Checked.
output is shown like this