Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

pascaldijkshoor
Contributor

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
Contributor II

Re: Select data the day before dimension date in set analysis

prevouis()

Re: Select data the day before dimension date in set analysis

1st expression

SUM(Amount)

2nd expression

Above(SUM(Amount))

pascaldijkshoor
Contributor

Re: Select data the day before dimension date in set analysis

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

Re: Select data the day before dimension date in set analysis

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
Contributor

Re: Select data the day before dimension date in set analysis

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

Re: Select data the day before dimension date in set analysis

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
Contributor

Re: Select data the day before dimension date in set analysis

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.

Community Browser