Set Analysis: Compare to value 3 days before

[felix_kraemer]

Dear all,

I struggle with following case.

I have a table with three columns. The third column should show the production of three days before.

Reservation Date	Count Confirmation	Count Confirmation -3 days
4.2.2020	100	70
3.2.2020	90	60
2.2.2020	80	...
1.2.2020	70	...
31.1.2020	60	...

set vDateMinus7 = [Reservation Date]-3
count({<[Reservation Date]={'=$(vDateMinus3)'}>} [Confirmation No])

But it still displays the same value as in column 2.

Thank you for any suggestions.

Regards

Felix  

[dplr-rn]

set analysis will not work here instead you can use below functions

https://community.qlik.com/t5/QlikView-Documents/Missing-Manual-Above-and-Below/ta-p/1481948

[robert99 · Accepted solution]

The reason why set analysis doesn't work is its not possible to link set analysis with a table dimension

(you can use set analysis if you filtered by one date only. but not a column of date dimensions)

The way Ive solved this issue in the past is to

• use above or below (see above) or
• use canonical dates

Example. I used this to have the MonthYear dimension and then to show the curr Monthyear vs previous Monthyear

DATEBridge:
Load
LINK_FactTable ,
DocPostingDate AS DateCanon,
'CurrYr' as CanonType
resident FACT_TABLES
// where DataType = 'InvOrCredits'
;

//AddMonths( DateCanon,+12) as FutureYrDate ,

DATEBridge: //prev year
Concatenate (DATEBridge)
Load
LINK_FactTable ,
addmonths(DocPostingDate,+12) AS DateCanon,   // use date(DocPostingDate +3) as DateCanon
'InvPastYear1' as CanonType      // 'Dateless3' as CanonType
resident FACT_TABLES
;

The DateCanon is linked to a calendar (although you wouldn't need this as its linked to DateCanon

The measures are

Current Year >>  sum({<CanonType = {CurrYr} >}InvoiceSalesNet)

Prior Year >>  sum({<CanonType = {InvPastYear1}>}InvoiceSalesNet)

[Almen]

Wouldn't Below(Confirmation No, 3)  work?

[robert99]

@Almen 

"Wouldn't Below(Confirmation No, 3)  work?"

It should do. Unless the user decides to drill into the data by date. Or sort the columns in some way. Then the chart might not show the correct values for Dateless3 days.

So what I tend to do is if its just for my use. Use below. If others are using it I prefer a more robust solution. So if the user drills down by date the chart still gives the correct total

[felix_kraemer]

Thank you all for your inputs. As I would like to display the -3 days value within a bar chart next to my actual value at the end I guess I need something like the script solution to have it more robust.