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.

[Gysbert_Wassenaar]

Create a Month field in the script to using the monthstart or monthend function. Add that as dimension in your chart and use two expressions max(ActivityDate) and firstsortedvalue(OccupUnitNo,-ActivityDate) to get the last day for each month and the matching OccupUnitNo for that day.

[ecolomer]

here you have an example

[ogster1974]

How did you upload your example?  I only have options to insert images or video in my edit screen which doesn't seem to like my qlik sense file.

[ecolomer · Accepted solution]

The script for tests objetifs:

// -----> 41275 = 01/01/2013

// -----> 41640 = 01/01/2014

// -----> 42005 = 01/01/2015

LET vMin=num(41275);

LET vMax=num(42005);

CAL:

LOAD

  Date(IterNo()+$(vMin)-1)   as xDate,

  Year(Date(IterNo()+$(vMin)-1)) as xYear,

  Month(Date(IterNo()+$(vMin)-1))  as xMes,

  Week(Date(IterNo()+$(vMin)-1))   as xWeek,

  WeekStart(Date(IterNo()+$(vMin)-1)) as xWeekStart,

  WeekEnd(Date(IterNo()+$(vMin)-1)) as xWeekEnd,

  MonthEnd(Date(IterNo()+$(vMin)-1) ) as xMonthEnd

  AutoGenerate 1 While IterNo()+$(vMin)-1<=$(vMax);

The field xMonthEnd have tha last day of Month

[ogster1974]

Sorry I meant how did you attach your qvw document to the reply

I have the option to upload image or video but not a document.  How did you attach it to your reply.

Thanks

[ecolomer]

For upload a file, you change Avanced editor (option on right-up corner) and you have this option

[#8]

Hi, thanks for the answer, I am new to scripts so what would the actual script look like please?

[#9]

Hi, when I load the model I get the fields but no data / tables / charts /scripts. What am I doing wrong please? Thank you.

[#10]

Do I just paste this in the script area?