Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need to report on data as at last day of the month

Hi,

I am relatively new to Qlik and so far am loving it.

I have a scenario where I collect data almost daily and one of the reports that I am trying to build is giving me a headache. My specs are as follows

Dimension = ActivityDate

Measure = OccupUnitNo

Trying to create a line chart that simply shows this data for the last in the calendar month that a report is submitted. This is not always the actual last day of the month as sometimes the data is not submitted due to being closed.

I have taken a look around and read lots of suggestions re flags, if statements, monthend() and I cant seem to make any of them work.

Would love some guidance please, thank you.

18 Replies
ecolomer
Master II
Master II

I don't understand

Not applicable
Author

I tried to load fechas.qvw but it seems to be empty. Perhaps I do not understand how to use these properly yet. My apologies.

ecolomer
Master II
Master II

repeat & try

Not applicable
Author

Do you use QlikView or QlikSense? The posted File is for QlikView, but we are in the QlikSense Area. With QlikSense you can only load the Datamodel (binary load) or migrate the Script from the QVW file.

If this Thread is about QlikView I can move it to the right Area.

ogster1974
Partner - Master II
Partner - Master II

This might do what you need ...

Take your data and create a calendar table the key of which is the last day of the month.

In my example

Jul was 01/08

Aug was 04/09

Sep was 30/09

Hope this helps.

Not applicable
Author

Hi, I am using Qlick sense desktop, thanks.

Not applicable
Author

I think you may be way more skilled than I. I have loaded this now and see where you have made the changes by adding a table but I cant seem to use it to manipulate the data I need. Do I just use =monthend(ActivityDate) as the Dimension or am I still missing something.

Not applicable
Author

Hi, so very close. The months do not display in the correct order (currently Aug Jul Sep on a line chart) and ideally I need the month and he year to display. Thanks.

ogster1974
Partner - Master II
Partner - Master II

I'll let you work on those items. 

It is possible to fix the ordering and create the desired chart with the data I provided in the example.

1. Use FiscalMonthNum in the sorting of your chart.  You can do this by sorting by expression.

2. Use FiscalYear and add as another dimension in your chart to bring Year into your view.