AddMonths to each Month Year from a different date

[Anonymous]

Hi Experts,

I have a simple requirement where my dimension consist of a DisbDate and ReportingDate.

Row = DisbDate

Column = ReportingDate

Measure = Sum(DisbAmt)

Find attached sample Qvf, data and Output sheet.

Data looks like :

DisbDate	01/12/2017	01/11/2017	01/10/2017	01/09/2017	01/08/2017	01/07/2017	01/06/2017	01/05/2017	01/04/2017	01/03/2017	01/02/2017	01/01/2017
01/12/2017	144	158	152	176	144	115	177	160	145	101	181	176
01/11/2017	-	118	177	161	149	155	195	198	109	136	188	101
01/10/2017	-	-	141	188	129	160	138	165	191	144	163	178
01/09/2017	-	-	-	129	164	169	135	135	108	128	123	174
01/08/2017	-	-	-	-	119	191	104	121	146	135	142	115
01/07/2017	-	-	-	-	-	130	104	136	170	115	144	161
01/06/2017	-	-	-	-	-	-	180	126	135	138	158	128
01/05/2017	-	-	-	-	-	-	-	180	116	124	129	105
01/04/2017	-	-	-	-	-	-	-	-	140	142	175	122
01/03/2017	-	-	-	-	-	-	-	-	-	159	150	199
01/02/2017	-	-	-	-	-	-	-	-	-	-	134	140
01/01/2017	-	-	-	-	-	-	-	-	-	-	-	103

I want to each row to start from 01/12/2017 instead of nulls.

I have also added a Sort field which would guide each row as how much columns to shift on the left. (Ref: Data Sheet, Tab2). The output should look as attached in "RequiredOutput" (Excel)

Would prefer something at Visual layer instead of load script.

[mdmukramali]

Dear Niky,

As per my understanding :

Kindly find the attached application i hope it will fulfill your requirement,

Thanks,

Mukram.

[Anonymous]

No buddy , this isn't that straight forward and sorting months will not help. I want the same output as attached in RequiredOutput excel

[sunny_talwar]

Some script changes will be needed to get this

New Script

MainTable:

LOAD

    DisbDate,

    DisbAmt,

    ReportingDate,

    DisbDate&ReportingDate as Key

FROM [lib://Lib/DataSheet.xlsx]

(ooxml, embedded labels, table is Tab1);

TempTable:

LOAD DisbDate

Resident MainTable;

Join (TempTable)

LOAD ReportingDate

Resident MainTable;

Concatenate (MainTable)

LOAD *

Resident TempTable

Where not Exists(Key, DisbDate&ReportingDate);

Drop Table TempTable;

MonthsToBeAdded:

LOAD DisbDate,

     Sort

FROM [lib://Lib/DataSheet.xlsx]

(ooxml, embedded labels, table is Tab2);

Basically added the missing combination of data between DisbDate and ReportingDate

and then this expression

After(Sum(DisbAmt), Sort)

[Anonymous]

Thanks Sunny !!

Would prefer something at the front end as editing load script is not possible currently.

[sunny_talwar]

Don't think it is possible, but let's see if someone can come up with a solution.

Best,

Sunny