Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
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
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
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)
Wouldn't Below(Confirmation No, 3) work?
"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
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.