Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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.

1 Solution

Accepted Solutions
ecolomer
Honored Contributor II

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

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

18 Replies

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

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.


talk is cheap, supply exceeds demand
ecolomer
Honored Contributor II

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

here you have an example

ogster1974
Honored Contributor II

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

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
Honored Contributor II

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

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
Honored Contributor II

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

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
Honored Contributor II

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

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

Not applicable

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

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

Not applicable

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

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

Not applicable

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

Do I just paste this in the script area?