Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Silvia
New Contributor

MAX Date of the last month end, whe weekly data is shown

I am trying to pull in the month end for set analysis and in a table; but my data has a rolling 2 week and month end.  The data is a rolling 12 month and most recent 2 weeks.  I need to have my calculations looking at only the month end data to trend month over month changes, but if I use MAX for the date it will pull in the weekly data.  Is there any way to create a variable to tell he formula to only look at month end data, and not use the weekly?

Example of the data:

LOAD Date

1/11/2019

1/4/2019

12/31/2018

11/30/2018

10/31/2018

Labels (4)
1 Solution

Accepted Solutions
pradosh_thakur
Honored Contributor II

Re: MAX Date of the last month end, whe weekly data is shown

ohk

Create a field in the script

load date,if(date=monthend(date),1,0) as monthend_flag

from table;

use max({<monthend_flag=1>}date)
Learning never stops.
4 Replies
pradosh_thakur
Honored Contributor II

Re: MAX Date of the last month end, whe weekly data is shown

compare the date with monthend(date) if same then consider else not.
Learning never stops.
Highlighted
Silvia
New Contributor

Re: MAX Date of the last month end, whe weekly data is shown

Not sure I understand.  How would I write that as a variable or in the formula?  I would need the most recent Month End date to appear.  In my example, at 1/11/2019 I would need 12/31/2018 to be the date to refer back to.  Same for 1/4/2019, I would need the formula to refer to 12/31/2018. 

pradosh_thakur
Honored Contributor II

Re: MAX Date of the last month end, whe weekly data is shown

ohk

Create a field in the script

load date,if(date=monthend(date),1,0) as monthend_flag

from table;

use max({<monthend_flag=1>}date)
Learning never stops.
Silvia
New Contributor

Re: MAX Date of the last month end, whe weekly data is shown

Thank you so much!