topic Re: calendar in QlikView

calendar

Wed, 18 Nov 2015 09:15:01 GMT

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

Re: calendar

sunny_talwar — Wed, 18 Nov 2015 09:21:22 GMT

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)

Re: calendar

Wed, 18 Nov 2015 09:23:36 GMT

sunny

thanks for the reply.

still showing '0' only.

Re: calendar

sunny_talwar — Wed, 18 Nov 2015 09:29:25 GMT

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

Re: calendar

Wed, 18 Nov 2015 09:32:50 GMT

i did selected quarter q4 and values are '0'

Re: calendar

sunny_talwar — Wed, 18 Nov 2015 09:36:40 GMT

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

Re: calendar

Wed, 18 Nov 2015 10:10:59 GMT

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

Re: calendar

tresB — Wed, 18 Nov 2015 10:31:52 GMT

Check if you are getting the value right in variable vMaxSalesDate

Re: calendar

Wed, 18 Nov 2015 10:39:37 GMT

yes, i m getting right value for the variable vMaxSalesDate

Re: calendar

Wed, 18 Nov 2015 10:54:33 GMT

somebody help pls

Re: calendar

sunny_talwar — Wed, 18 Nov 2015 10:57:01 GMT

How do you define vMaxSalesDate?

Re: calendar

Wed, 18 Nov 2015 11:01:41 GMT

i defined

vMaxSalesDate= Max(Date)

Re: calendar

sunny_talwar — Wed, 18 Nov 2015 11:07:28 GMT

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.

Re: calendar

Wed, 18 Nov 2015 11:15:28 GMT

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

Re: calendar

sunny_talwar — Wed, 18 Nov 2015 11:17:17 GMT

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?

Re: calendar

Wed, 18 Nov 2015 11:22:01 GMT

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

Re: calendar

sunny_talwar — Wed, 18 Nov 2015 11:33:01 GMT

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

Re: calendar

Thu, 19 Nov 2015 08:56:25 GMT

thanks a lot sunny

Re: calendar

sunny_talwar — Thu, 19 Nov 2015 11:16:04 GMT

No problem my friend.

Best,

Sunny