Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Set Analysis: Compare to value 3 days before

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 DateCount Confirmation

Count Confirmation -3 days

4.2.202010070

3.2.2020

9060
2.2.202080...
1.2.202070...
31.1.202060...

 

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  

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Re: Set Analysis: Compare to value 3 days before

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)

View solution in original post

5 Replies
Highlighted
Partner
Partner

Re: Set Analysis: Compare to value 3 days before

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

Highlighted
Specialist III
Specialist III

Re: Set Analysis: Compare to value 3 days before

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)

View solution in original post

Highlighted
Contributor II
Contributor II

Re: Set Analysis: Compare to value 3 days before

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

Highlighted
Specialist III
Specialist III

Re: Set Analysis: Compare to value 3 days before

@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

Highlighted
Contributor III
Contributor III

Re: Set Analysis: Compare to value 3 days before

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.