Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pascaldijkshoor
Creator
Creator

Select data the day before dimension date in set analysis

Hi everyone,

I need a formula that selects data from the day before the shown dimension date on the actual dimension date. 

I made an example of what I need in the table below. I should mention that the formula i want to create in the end takes data from the same day + data from the previous day, so it is not possible to change the formula of the date in the dimension field. I have tried to explain this as simple as possible.

   

DateActual figuresRequired from set analysis
1-4-201825-
2-4-20184025
3-4-20183540
4-4-20183035
5-4-20182030
6-4-20184520
7-4-20186045

I have tried the following two formulas:

sum({<[L_BEGIN_DATE.autoCalendar.Date]={'$(=Date(today()-1))'}>}LOAD_INDEX)

This formula shows only the result of yesterday. So today it's 4th of April, so in the example table Qlik would only show the figure of the 3rd of April, which is 40.

SUM({<[L_BEGIN_DATE.autoCalendar.Date]={'$(=Date(Max([L_BEGIN_DATE.autoCalendar.Date])-1))'}>}LOAD_INDEX)

This formula shows only the result of the last date that is part of the table -1. So the last date is 7th of April, and Qlik would show the figure of 6th of April, which is 20.

These formulas gave me only the result of 1 date. But i have a table with the data as a dimension, and I need a formula that shows me results of the day before the dimension date on the actual dimension date.

Is there a formula that can do this?

7 Replies
gerry_hdm
Creator II
Creator II

prevouis()

MK_QSL
MVP
MVP

1st expression

SUM(Amount)

2nd expression

Above(SUM(Amount))

pascaldijkshoor
Creator
Creator
Author

How should I integrate previous () in my formula? I have tried the following formula but this gives no results:

sum({<[L_BEGIN_DATE.autoCalendar.Date]={'$(=Date(previous()))'}>}LOAD_INDEX

sunny_talwar

This is not possible to do using set analysis... you can do as mrkachhiaimp‌ pointed out or look into implementing The As-Of Table

pascaldijkshoor
Creator
Creator
Author

Thanks for everyone's input. What I want to achieve is possible with the above function. The only thing is i don't get the correct value when there is no data on a date. As you can see in the image below, column 1178 gives on 1/8/2018 a value of 42:43:00. However a part of this value should be the value on 1/7/2018. So 8 January takes the data of 6 January. 

Do you know a way solve this?

$41A32ACD938071D6.jpg

sunny_talwar

I would def. suggest to use The As-Of Table here or may be share a sample so that we can look at this

pascaldijkshoor
Creator
Creator
Author

I looked into the As-Of Table, but i don't understand how this would help me. Can you explain how you see this?

Unfortunately i am not allowed to share any samples.