topic Re: All Months & Quarters Should Display. in QlikView

All Months & Quarters Should Display.

MuraliPrasath — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I've a date only with last Quarter information but on the front-end I would like to display all Months ( Jan to Dec) and Quarters ( Q1, Q2, Q3,Q4). When i use the Master Calendar I see only the months and quarter that i've in my data.

I used this Master Calendar.

MasterCalendar:

Load

TempDate AS DATE,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

;

//=== Generate a temp table of dates ===

LOAD

date(mindate + IterNo()) AS TempDate

,maxdate // Used in InYearToDate() above, but not kept

WHILE mindate + IterNo() <= maxdate;

//=== Get min/max dates from Field ===/

LOAD

min(FieldValue('DATE', recno()))-1 as mindate,

max(FieldValue('DATE', recno())) as maxdate

AUTOGENERATE FieldValueCount('DATE');

*************************************************

I see the below Quarters and Months in respect to my data. But i wanted to dispay all Quarters and Months.

Please help me!

Re: All Months & Quarters Should Display.

adamdavi3s — Fri, 09 Dec 2016 07:04:34 GMT

Ok so you need to generate more data using your calendar script.

At the moment it is only loading data between your min and max DATE

Try using the script like below (you might want to tweek the date format etc)

LOAD

Date('01/01/2016') as mindate,

Date('31/12/2016') as maxdate

AUTOGENERATE 1;

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

This will help users identify the answers should they come across this thread in the future.

Re: All Months & Quarters Should Display.

trdandamudi — Fri, 09 Dec 2016 07:07:07 GMT

Based on your data, you have only Q4 and that is the reason you are seeing only three months. If you need to see the remaining quarters and the months then make sure your minimum date starts from 01/01/2016.

Re: All Months & Quarters Should Display.

Fri, 09 Dec 2016 07:08:37 GMT

You have to generate dummy dates which they fill all your needs, then you derive your fields

Re: All Months & Quarters Should Display.

trdandamudi — Fri, 09 Dec 2016 07:27:55 GMT

Attached is a sample master calender which I generated for the year 2016. Take a look and adopt the code as per your needs. Hope this helps.....

Re: All Months & Quarters Should Display.

arasantorule — Fri, 09 Dec 2016 07:34:28 GMT

Hi,

The data is only for three months and your calendar is created based on them.

So It will show the available months in the UI.

LOAD

yearstart(FieldValue('DATE', recno()))-1) as mindate,

yearend(FieldValue('DATE', recno()))) as maxdate

AUTOGENERATE 1;