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...

1 Solution

Accepted Solutions
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))

View solution in original post

18 Replies
sunny_talwar

I think you need to add all the calendar fields to your expression wherever you would possible be making selections:

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

Not applicable
Author

sunny

thanks for the reply.

still showing '0' only.

sunny_talwar

You are making selection in one of the two fields I added to the expression right?

Not applicable
Author

i did selected quarter q4 and values are '0'

sunny_talwar

I am not sure what might be causing it. Would you be able to share a sample?

Not applicable
Author

sunny,

i think the problem is here. (Monthstart)

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

if i replace it with Quarterstart, its working fine when i select quarter.

but my requirement is to give Monthtodate, quarter selection, date selection evering in single expression.

how can i get it. pls help

tresesco
MVP
MVP

Check if you are getting the value right in variable vMaxSalesDate

Not applicable
Author

yes, i m getting right value for the variable vMaxSalesDate

Not applicable
Author

somebody help pls