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

[#1]

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.

[ecolomer]

I don't understand

[#3]

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]

repeat & try

[#5]

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]

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.

[#7]

Hi, I am using Qlick sense desktop, thanks.

[#8]

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.

[#9]

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]

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.