
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 :
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear Niky,
As per my understanding :
Kindly find the attached application i hope it will fulfill your requirement,
Thanks,
Mukram.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
No buddy , this isn't that straight forward and sorting months will not help. I want the same output as attached in RequiredOutput excel

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Sunny !!
Would prefer something at the front end as editing load script is not possible currently.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Don't think it is possible, but let's see if someone can come up with a solution.
Best,
Sunny
