Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

AddMonths to each Month Year from a different date

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 :

 

DisbDate01/12/201701/11/201701/10/201701/09/201701/08/201701/07/201701/06/201701/05/201701/04/201701/03/201701/02/201701/01/2017
01/12/2017144158152176144115177160145101181176
01/11/2017-118177161149155195198109136188101
01/10/2017--141188129160138165191144163178
01/09/2017---129164169135135108128123174
01/08/2017----119191104121146135142115
01/07/2017-----130104136170115144161
01/06/2017------180126135138158128
01/05/2017-------180116124129105
01/04/2017--------140142175122
01/03/2017---------159150199
01/02/2017----------134140
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.

5 Replies
mdmukramali
Specialist III
Specialist III

Dear Niky,

As per my understanding :

282221.PNG

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

Thanks,

Mukram.

Anonymous
Not applicable
Author

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

Capture.PNG

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
Not applicable
Author

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