Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

calendar

Hi Experts,

This is my master calendar

Temp:

Load

               min(Date) as minDate,

               max(Date) as maxDate

Resident Sales;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

     Set vCal_FD = 3; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}

     Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}

     Set vCal_RD = 4; // Reference day = This day in Jan defines week one {1..7}

   

   

QuartersMap:

MAPPING LOAD 

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

TempCalendar:

LOAD

               $(varMinDate) + Iterno()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

  Load *, DEC_WeekYear & '-' & NUM(DEC_WeekNumber,'00') as DEC_YearWeek;

     Load *,

          Div( Date - WeekStart( DEC_WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as DEC_WeekNumber,

          Year( DEC_WeekYearRefDate ) as DEC_WeekYear;

     Load *,

          Date( YearStart( If( $(vCal_BW), Date, DEC_WeekRefDate )) + $(vCal_RD) - 1) as DEC_WeekYearRefDate ;

     Load *,

          Date( WeekStart( Date, 1, $(vCal_FD) ) - $(vCal_RD) ) as DEC_WeekRefDate ;

Load

               TempDate AS Date,

               TempDate AS DEC_Date,

               NUM(TempDate) AS DEC_NumDate,

               WeekStart(TempDate, 0, $(vCal_FD) ) as DEC_WeekStart ,

               Week(TempDate) as DEC_Week,

               Year(TempDate) As DEC_Year,

               Month(TempDate) As DEC_Month,

               MonthName(TempDate) As DEC_MonthYear,

            IF(TempDate<Today(),1,0) as  DEC_TD,

                Day(TempDate) As DEC_Day,

               YeartoDate(TempDate)*-1 as DEC_CurYTDFlag,

               YeartoDate(TempDate,-1)*-1 as DEC_LastYTDFlag,

               If(InMonthToDate(TempDate, '$(vToday)'-1,0),1,0) as DEC_CurMTDFlag,

                        

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

               RIGHT(Year(TempDate) & ' ' & ApplyMap('QuartersMap', month(TempDate), Null()), 2) AS CalendarQuarter,

             

               If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-1),0),1,0) as DEC_LastMTDFlag,

               If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-2),0),1,0) as DEC_PrevMTDFlag,

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as DEC_RC12,

               WeekDay(TempDate) as DEC_WeekDay       

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

i need to do a calculation like

SUM({<DEC_MonthYear =,DEC_Date= {">=$(=date(MonthStart(vMaxSalesDate)))<=$(=date(vMaxSalesDate))"}>}Points)

where vMaxSalesDate=Max(Date)

when i select different month or different date, i m getting expected values. but when i select a quarter, i m getting all '0'.

how should i change my expression or anything wrong in my calendar?

Pls help...

18 Replies
sunny_talwar

How do you define vMaxSalesDate?

Not applicable
Author

i defined

vMaxSalesDate= Max(Date)

sunny_talwar

Lets say you select quarter of Q2 (1st Apr 2015 - 30th Jun 2015)

You would like to see the result for >=1st June 2015<=30th June 2015, is that right?

Now put this in a text box object and see if you get that range in the format of your date field

='>='& Date(MonthStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate)

If you do, then try this:

Sum({<DEC_MonthYear =, Quarter = , CalendarQuarter =, DEC_Date= {"$(='>='& Date(MonthStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate))"}>}Points)


If you don't, we will have to start fixing this first.

Not applicable
Author

sunny,

i have only 3 months data. i.e., sep 2015 to nov 2015

Quarters Q3 & Q4.

i tried this expression in a text box

='>='& Date(MonthStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate)

when i selected Q3, i m getting date range as 01/09/2015 to 30/09/2015

but when i select Q4, i m getting date range as 01/11/2015 to 18/11/2015 which is not correct.

i should get 01/10/2015 to 18/11/2015 for Q4 selection.

pls help me to correct this

sunny_talwar

Why would you get 01/10/2015? MonthStart for 18/11/2015 is 01/11/2015? What is your logic for seeing 01/10/2015?

Not applicable
Author

sunny,

my pivot table expressions are MTD calculations with the set analysis expressions

Sum({<DEC_MonthYear =, DEC_Date= {"$(='>='& Date(MonthStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate))"}>} field)

But my client needs quarterly values too in the same table when they select the quarters Q3 or Q4.

is that possible with above expression?

if not, how should i change the above expression?

pls help me out

sunny_talwar

So if quarter is selected you would like to see quarterly info and when its not you would like to see monthly info? Is that correct? If it is then, may be this:

If(GetSelectedCount(Quarter) = 1, Sum({<DEC_MonthYear =, DEC_Date= {"$(='>='& Date(QuarterStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate))"}>} field), Sum({<DEC_MonthYear =, DEC_Date= {"$(='>='& Date(MonthStart(vMaxSalesDate)) & '<=' & Date(vMaxSalesDate))"}>} field))

Not applicable
Author

thanks a lot sunny

sunny_talwar

No problem my friend.

Best,

Sunny