Logic

[panipat1990]

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

[MarcoWedel]

Hi,

can you please explain where the month and quarter related information is coming from?

thanks

regards

Marco

[swuehl]

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

[amayuresh]

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