# Qlik Sense App Development

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.

 Date Actual figures Required from set analysis 1-4-2018 25 - 2-4-2018 40 25 3-4-2018 35 40 4-4-2018 30 35 5-4-2018 20 30 6-4-2018 45 20 7-4-2018 60 45

I have tried the following two formulas:

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.

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

prevouis()

MVP

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

1st expression

SUM(Amount)

2nd expression

Above(SUM(Amount))

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:

MVP

## 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

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?

MVP

## 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

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.