Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

asb02512
New Contributor III

Calendar Issue

Hello Everyone,

please find the attached Img of visualization currently i am working on. In this One i am facing small issue. When i am trying to take Year (fig1.1.year) as Dimension. All Months Are not displayesd AUG and SEP is missing. i tried to resize the length of the graph still there is no use. And, In the weeks ((fig1.2.weeks). Is there any possibility to display weeks as "Week-1.....Week-53" but not as the numbers as shown 2-2016,3-2016,........

CALENDAR SCRIPT:-

MinMax:

LOAD

  Min(SL_DATE) as MinDate,

  Max(SL_DATE) as MaxDate

RESIDENT SALES_HDR;

// //Set script variables with min and max order dates

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

//Generates a single table with one field containing

//all existing dates between MinDate and MaxDate.

TempCal:

LOAD

  Date($(vMinDate) + RowNo() - 1) as TempDate

AutoGenerate

  $(vMaxDate) - $(vMinDate) + 1;

   

//Drop MinMax table - best practice to drop tables as you go to free up memory

DROP TABLE MinMax;

//Create the Master Calendar

MasterCalendar:

LOAD

  *,

  Week&'-'&If(Week=1 AND Month=12,Year+1, If(Match(Week,52,53) AND Month=1, Year-1, Year)) as WeekYear;  

LOAD

  Floor(TempDate) as SL_DATE,

  Week(TempDate) as Week,

  Year(TempDate) as Year,

  Month(TempDate) as Month,

  Day(TempDate) as Day,

  WeekDay(TempDate) as WeekDay,

  'Q' & Ceil(Month(TempDate) / 3) as Quarter,

    Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,

  InYearToDate(TempDate, $(vToday), 0) * -1 as CurYTDFlag,

  InYearToDate(TempDate, $(vToday), -1) * -1 as LastYTDFlag,

   

    If(DayNumberOfYear(TempDate) <= DayNumberOfYear($(vToday)), 1, 0 ) as IsInYTD,

  If(DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter($(vToday)), 1, 0) as IsInQTD,

    If(Day(TempDate) <= Day($(vToday)), 1, 0) as IsInMTD,

    If(Month(TempDate) = Month($(vToday)), 1, 0) as IsCurrentMonth,

    If(Month(AddMonths(TempDate,1)) = Month($(vToday)), 1, 0) as IsLastMonth

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP FIELDS Week, MonthYear; //Created as example for UI dimension

//Drop the TempCal table as it is no longer reuired

DROP TABLE TempCal;

1 Solution

Accepted Solutions
vinieme12
Esteemed Contributor II

Re: Calendar Issue

Just use

'W-'&Week  >> Week from your calendar table

7 Replies
MVP
MVP

Re: Calendar Issue

Your calendar script seems quite ok. Can't see any problems with it.

Your can get the WeekYear to have a W in front by simply adding it to the appropriate load column like this:

'W' & Week&'-'&If(Week=1 AND Month=12,Year+1, If(Match(Week,52,53) AND Month=1, Year-1, Year)) as WeekYear

When it comes to the missing months it must be something with your data table of sales or something with how you created you line chart. I did a test run in my environment and have no problems with missing months based on your master calendar script. Could you share a sample app to illustrate the problem?

vinieme12
Esteemed Contributor II

Re: Calendar Issue

Can you post the expression you used for both these charts you might be missing values for those months for a certain measure which is resulting in null() or zero values

asb02512
New Contributor III

Re: Calendar Issue

Dear Mr. Petter

Thanks a lot for the Solution of weeks, But  sir, here the requirement is to hide the year from Week year.

Current Week year : 1- 2016, 2-2016, 3-2016....

After your Sol: W-1-2016,W-2-2016,W-3-2016,.....

Requirement is: W-1, W-2, W-3, W-4.............

vinieme12
Esteemed Contributor II

Re: Calendar Issue

Just use

'W-'&Week  >> Week from your calendar table

asb02512
New Contributor III

Re: Calendar Issue

58b9316848914d39a452b1454718fe9b.png

Sum({$<Year={2016}>}NET_SALE_VALUE)-Sum({$<Year={2016}>}NET_SALE_RETURN_VALUE)

Sum({$<Year={2017}>}NET_SALE_VALUE)-Sum({$<Year={2017}>}NET_SALE_RETURN_VALUE)



asb02512
New Contributor III

Re: Calendar Issue

Thanks Vineeth your other message "Left(WeekYear,len(WeekYear)-5)" is not appearing here but i got it on mail and this works for weeks..

asb02512
New Contributor III

Re: Calendar Issue

Thanks Vineeth your other message "Left(WeekYear,len(WeekYear)-5)" is not appearing here but i got it on mail and this works for weeks..