Filter by date of the previous month

[Nicolas07]

Hello everybody,

I'm new to Qlik sense and I'm looking for a formula which is quite complex for me.

I'm trying based on a column in which you have some date like 03/2018 to filter base on the month of this date in my foruma.

For example if : 03/2018 then month = 3 and in my case I want to display the value (of another column) which is the value of the month -1 so back to my example I want to display the corresponding value of the month 2 in the other column with this case :

Date :                     Stock :

02/2018                1

03/2018                20

04/2018                12

In this case I want to filter on the value 1. Someone can help me?

Best regards,

Nicolas

[jwjackso]

If there are no gaps in your date column, you can try the Above function.

[Nicolas07]

Hi Jwjackso

first thanks a lot for your help, this fonction is interresting, but I have gaps like below :

date :             Stock :           Code :

01/2018        20                     340019

01/2018        12                     340000

02/2018        14                     340019

and when I select code 340019 date 02/2018 I want to be able to see stock 20. Do you think it’s possible?

[jwjackso]

How is the data visualized, is it in a table or will the qty 20 be in a text box?  In the example that you have, is the 02/2018 the max date for code 340019?  Do you have the date fields associated with a calendar table?

[Nicolas07]

Hi JwJackso,

Again, thanks for your help, I will use the result in a table and in a line chart within Qlik Sense.

Unfortunatelly, the date 02/2018 won't be the max date for this code, actually, every month I'll have to update with some new stock data for each code.

The calendar table will start from 01/2017 and I'll have some data for each month until 03/2019 and next month I'll add some data for 04/2019.

Do you think we can do something to manage this?

Nicolas