Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

panipat1990
Contributor II

Logic

Hi Team,

I have a table in this format

Header 1Header 2Header 3Header 4Header 5Header 6Header 7Header 8Header 9
ParticulersBudgetActual
Net Revenue1500016000
Selling exps2000022000
Net realisation670006543
other54329876
expection1000011000
gain78968976

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 1Header 2Header 3Header 4Header 5Header 6Header 7Header 8Header 9Header 10Header 11Header 12Header 13Header 14Header 15Header 16Header 17
PariculersAprMayJunQ1JulAugSepQ2OctNovDecQ3JanFebMarQ4
Net Revenue1500160017004800120012001200360011001100110033001300130013003900

Same For Selling Exps,Net Realisation ,Other, Expection, Gain

3 Replies

Re: Logic

Hi,

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

thanks

regards

Marco

MVP
MVP

Re: Logic

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
Contributor III

Re: Logic

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

1.PNG