One more field from existed field

[#1]

Hi All,

can any one help me on the below scenario,

I have a table with ID, Firm ,
   

Year, Period,Objective GLObject ,Sales, now I want a table with current year sales and Last year Sales in the same table , how to create these two fields in the same table. any help can be appreciated.

Please find the Sample data.

Regards

John

[#2]

Yes Neetha i did it,however when come to  the below step

Left Join(Data)

LOAD Unique ID,

Sales as LastYearSales

Resident Data

Where Year = ($(vMaxYear) - 1);

QV will read the last year Unique values it will not read the Current year values in that case last year values will not populate for the current year selection. How do I over come this.

[Anonymous]

May be try:

Left Join(Data)

LOAD  ID,

Sales as LastYearSales

Resident Data

Where Year >= ($(vMaxYear) - 1);

[sunny_talwar]

Try this:

TABLE:

LOAD ID,

    Firm,

    Objective,

    Year,

    Period,

    [GL Object],

    Sales

FROM

community6.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join (TABLE)

LOAD 'FY' & Max(Right(Year, 2)) as Year,

  1 as Flag1

Resident TABLE;

Left Join (TABLE)

LOAD 'FY' & Max(Right(Year, 2), 2) as Year,

  1 as Flag2

Resident TABLE;

FinalTable:

LOAD *,

  If(Flag1 = 1, Sales) as CurrentYearSales,

  If(Flag2 = 1, Sales) as PreviousYearSales

Resident TABLE;

DROP Table TABLE;

[#5]

Thanks Sunny, for your efforts ,however I am expecting output like

in the image you have added the Previous year sales for the FY14 is null but it should not be like that we have sales for the Previous year FY13 those values has to come  beside the Current year sales.

Hope you get it.

Regards

John

[Anonymous]

hi john,

sunny's solution reflects your requirement.

try to remove year field in chart in UI and you will get same output as your requirement

[#7]

Thanks Neetha , I tried, however it is not working.

you can find same sample data with Expected result. in the attachment.

Regards

John