How to create the Quarter and year tabs?

[#1]

Friends please help me out with creating tabs which contains selection of the year and particular quarter and displays records according to it

[amit_saini · Accepted solution]

Hi Vijay ,

Please try this: (Suppose you are having date field by the name of DateID)

CAL:

Load

    DateID,

    Month(DateID) as SALES_MONTH,

    Year(DateID) as SALES_YEAR,

    MonthName(DateID) as SALES_MONTHNAME,

    'Q' & Ceil(Month(DateID)/3) AS SALES_QUARTER,

    DayNumberOfYear(DateID) as DayNumberOfYear,

    Day(DateID) as DayNumberOfMonth,

    DayNumberOfQuarter(DateID) as DayNumberOfQuarter,

    num(Month(DateID)) as MonthNum

    Resident Cal;

drop Table Cal;

In this manner you can create year, month, quartet, week, day inside script and call them in a list box to display them in layout the way you want.

Hope this will help you.

Thanks,

AS

[ashfaq_haseeb]

Hi,

use the below script.

Let varMinDate = num(MakeDate(2000,1,1));
Let varMaxDate = num(MakeDate(2020,12,31));


//*************** Temporary Calendar ***************
TempCalendar:
LOAD
  $(varMinDate) + rowno() - 1 AS Num,
  date($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
  $(varMaxDate) - $(varMinDate) + 1;




//*************** 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

RESIDENT TempCalendar
ORDER BY TempDate ASC;


DROP TABLE TempCalendar;
Store MasterCalendar into MasterCalendar.qvd(qvd);

Regards

ASHFAQ

[sudeepkm]

You need to create a quarter field in your table. in case your date field name is period then you can use the expression below to create a quarter.

'Q' & ceil(month(Period)/3) as Quarter,

Year(Period) as Year

You can have these fields in your list box so that end users can filter select the year and then they can select a quarter and based on the data association the whole dashboard will display the results.

[#5]

Thanks amit saini. Its working (Y)

[amit_saini]

Hi Vijay,

If it is working , please mark as current answer to close the thread.

Thanks,

AS