Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
pas_epm_support
New Contributor II

Cummulative LTM wrt to an island Date

Hi, 

I have a main table with a field called "originalDate","fund", "strategy", "DVA" etc and I have an island table with a field called "selectedDate". 

If i select a date using field "originalDate", I am able to get the cumulative DVA for last 12 months with the formula:

Num((sum(
aggr(
rangesum(above( sum({$<originalDate>} dva),0,12))
,strategy, originalDate)
))/1000000,'#,###')

However, i don't want to use the field "originalDate" for my date selection. I want to select a date using the field "selectedDate" which is a field from island table and get the same cummulative DVA based on the selectedDate.

Please help.

Load script is as below : 

RestConnectorMasterTable:
SQL SELECT
"originaldate",
"fund",
"dva",
"nav",
"strategy"
FROM JSON (wrap on) "root";

[EPM_Fund_Data]:
LOAD [originaldate]& '|' & [strategy] AS KEY,
[originaldate] AS [originaldate],
[fund] AS [fund],
[dva] AS [dva],
[nav] AS [nav],
[strategy] AS [strategy]

RESIDENT RestConnectorMasterTable;

NoConcatenate
SelectableDates:
LOAD distinct
originaldate as originaldate
resident RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;