Cumulative AS AT Position

[Jason]

Hi,

The Data warehouse that we are building the Qliksense dashboard app off is a contra-correction Database.   I want to use a date picker to get an AS AT position in history. 

The Facts use contra correction logic to give movements if there is a change, eg If a Fact changes from 10,000 to 11,000 on day 2, and again to 12,000 on Day 5

Day 1 : Fact Value = 10,000

Day 2 : Fact Value = -10,000

Day 2 : Fact Value = 11,000

Day 5 : Fact Value = -11,000

Day 5 : Fact Value = 12,000

Thus we know that on Day 1 the Value was 10,000, an that the movement on day 2 was 1,000, but the AS AT for Days 2,3 and 4 is 11,000, and by Day 5 its 12,000.  The Time slice on the Warehouse uses ReportedDate.

When I introduce a Datepicker using the ReportedDate i just get the movements on that day, when I want to SUM(Fact Value) WHERE ReportedDate <= Datepicker value. 

Any Ideas how to accomplish this?

[Digvijay_Singh]

Pl share sample data and expected output, that will help to build script faster. Not able to clearly visualize what is expected when we make selections in the date picker, lets say 09/10/2023

[Jason]

Sorry, I have attached a simple CSV with the Data

The core of it is that on a Table I want to see One row with the AS AT value 

Date	Movement	AS AT Value
10-Sep-23	10000	10000
11-Sep-23	1000	11000
12-Sep-23	0	11000
13-Sep-24	0	11000
14-Sep-23	1000	12000

If I have a Date picker and select 12-Sep-2023, I should See 11,000, but if I select 14-Sept I should see 12,000.

Obviously there are more attributes but they have no bearing on the Output.

Ih TSQL the Query would be

SELECT SUM(Value) FROM TABLE WHERE ReportedDate<='12-Sep-2023' (or 14-Sept-2023).

[Digvijay_Singh · Accepted solution]

I would prefer using As Of Date calendar in such situation but not sure if it fit to your other needs - 

fact:
Load Date#(ReportedDate,'DD-MMM-YY') as [_date_key],
Value
inline [
ReportedDate, Value
10-Sep-23, 10000
11-Sep-23, -10000
11-Sep-23, 11000
14-Sep-23, -11000
14-Sep-23, 12000
];

NoConcatenate
master_calendar:
//=== Generate a temp table of dates ===
LOAD
[Cal Date],
[Cal Date] as _date_key;
LOAD
date(mindate + IterNo(),'DD-MMM-YY') as [Cal Date]
WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('_date_key', recno()))-1 as mindate,
max(FieldValue('_date_key', recno())) as maxdate
AUTOGENERATE FieldValueCount('_date_key');

// ======== Create a list of distinct Dates========
tmpAsOfCalendar:
Load distinct [Cal Date]
Resident [master_calendar] ;

// ======== Cartesian product with itself ========
Join (tmpAsOfCalendar)
Load [Cal Date] as [As of Cal Date]
Resident tmpAsOfCalendar ;

[As-Of-Date Calendar]:
Load [Cal Date],
[As of Cal Date],
Round([As of Cal Date]-[Cal Date]) as DateDiff
Resident tmpAsOfCalendar
Where [As of Cal Date] >= [Cal Date]
;
Drop table tmpAsOfCalendar;

[Jason]

Thank you,

I took a slightly different tack and used a Calendar Dimension that wasn't attached to the Data, this allowed me to do 

=SUM(IF(ReportedDate<=GetSelectedItem(CalendarDate),SUM(Measure)).