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.
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.
Highlighted
Silvia
New Contributor

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

Thank you so much!