Max Date minus Next Highest Date

[skilloran]

Hello,

For one of the project I am on, I need to subtract the highest selected dates values from the next highest selected date.

Essentially we may not have data everyday, so if the user selects the date May 1st 2021 for example, we do not necessarily want April 30th 2021 to be the previous date we are looking it. If say we do not have values for April 30th but we do for April 29th, we then would want to subtract May 1st's values from April 29th (skipping the null value on April 30th)

My main issue is I am able to get this to work when NO selections are made. This is because I have the tables defaulted to the max date and when subtracting the previous date it is actually looking at/including all previous dates, so it knows that the previous date is an option

However when I select one date I would expect it to still look at the previous date's value, but it does not and it defaults to zero.

How do I get it to work without making the user manually select BOTH dates?

Work Below:

Variables and Defined Objects:

DateOrder creates row numbers so each date entry is given a number (first date entered receives a 1, second date entered receives a 2 and so forth) **Only dates with data are loaded into the application

vMaxDateNo = max(DateOrder)

PreviousMaxDate = max(DateOrder) - 1

TestPreviousMaxDate = if(GetFieldSelections(Date) = 0, max(DateOrder), max({$}DateOrder)-1)

Table={'A'} is used to define which values go into which tables (user wants things split up very specifically)

Some of the solutions I have tried are:

Sum({$<DateOrder={$(vMaxDateNo)},Table={'A'}>}Value) - Sum(${<DateOrder={$(testpreviousmaxdate)},Table={'A'}>}Value)

and 

Sum({$<DateOrder={$(vMaxDateNo)},Table={'A'}>}Value) - Sum(${<DateOrder={$(PreviousMaxDate)},Table={'A'}>}Value)

[chrismarlow]

Hi,

When your user is selecting a date are they actually making a selection on a field with the date it rather than DateOrder? This would explain why you get zero, as the date selection is not 'cancelled' by using the set analysis.

Are you really using DateOrder to try to get around missing dates? If so I think if you just use the date field in your set analysis and Max(datefield) Max(datefield,2) for your dates - the in the second the second parameter being the rank. Note if you make other more granular selections this could get some effects where you have genuine gaps in the data.

I have now and again found I've had to do things with formatting of dates to get it work in the set analysis expression, but it can be done.

Cheers,

Chris.

[skilloran]

They are making a selection on the date field not date order. 

Would doing it the way you listed above cover the large amounts of dates? I attempted using just the date field and it still just gives me null values. 

[chrismarlow]

Hi,

Actually what I suggested would not work, there is a solution here though;

Solved: set analysis when max(date, 2) not working when se... - Qlik Community - 1655921

So you would be looking at something like;

sum({<Table={'A'},DateField={'$(=Date(Max(DateField)))'}>}Value)
-sum({<Table={'A'},DateField = {"$(=Date(Max({<DateField = {[<$(=Max(DateField))]}>}DateField)))"}>}Value)

Cheers,

Chris.

[skilloran · Accepted solution]

The answer ended up being not using set analysis at all

I ended up having to create a new field in the data model using the below script:

DeltaVariance:
Load
Desk,
[%CDATE],
Value,
ValueType,
FactType,
ApplyMap('DeskTableMap', Desk, 'NoMap') as Table,
If(Previous(Desk)=Desk and Previous(ValueType)=ValueType and Previous(FactType)=FactType and Previous(Table)=Table,(Value-Previous(Value)),0) as PreviousVariance
Resident Facts
Order By Desk,ValueType,FactType,Table,%CDATE;

Essentially saying if the desk, valuetype, facttype and table match, then subtract the previous value from the current value.