Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
asb02512
Contributor III
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
Champion III
Champion III

Just use

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

7 Replies
petter
Partner - Champion III
Partner - Champion III

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
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
asb02512
Contributor III
Contributor III
Author

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
Champion III
Champion III

Just use

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
asb02512
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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