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: 
Not applicable

How to group time ranges in different columns?

Hi All,

I need to create a table looks like below. I tried to create all these columns in pull script and put them in pivot table but it does not look right. Would you please let me know how to create table such as this in Qlikview? 

screenshot.png

I have attached the dummy data file in this post at the bottom, sales and unit just need to be sum.

Thank you for your input everyone.

4 Replies
vinieme12
Champion III
Champion III

This should give you a hint how to

Period Presets: Compare Periods on the fly

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

I will look into that. Thank you.

Not applicable
Author

Hi Vineeth,

I was able to look into your example and it was what I needed. But I ran into problem that the period did not show everything. I have date range from 2016-12-01 to 2017-02-09, but period only shows time elements for 2017. Would you please take a look at the pull script below and tell me what I did wrong? Thank you for your help.

MinMax:

LOAD

  min(Date) as MinDate,

  max(Date) as MaxDate

RESIDENT IVR_Dashboard;

//Assign min and max dates to variables

Let vMinDate = NUM(Peek('MinDate',0,'MinMax'));

Let vMaxDate = NUM(Peek('MaxDate',0,'MinMax'));

Let vToday = num(Date(Now()));

//Creating the TEMP calendar, populating missing dates

TempCal:

LOAD

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

Autogenerate

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

let vToday = NUM(date(now()));

MasterCalendar:

LOAD

TempDate as Date,

AutoNumber(TempDate) as DateNum,

'FY-' & Year(TempDate) as FullYear,

NUM(YEAR(TempDate)&num(Month(TempDate),'00'),'0')  as  YearMonth,

//YEAR(TempDate)&Month(TempDate)

'Q' & Ceil(Month(TempDate)/3) & '-' &  Year(TempDate) as QuarterYear,

'H' & Ceil(Month(TempDate)/6) & '-' & Year(TempDate) as HalfYear,

if((InYearToDate(TempDate,$(vToday),0) * -1)>0,'YTD-'&Year(now())) as CurYTDFlag,

if((InYearToDate(TempDate,$(vToday),-1) * -1)>0,'YTD-'&(Year(now())-1)) as LastYTDFlag

RESIDENT TempCal

Order By TempDate ASC;

TEMP_PRESET:

CrossTable (PeriodCat , Period , 2)

LOAD *

Resident MasterCalendar;

DROP TABLES MasterCalendar;

PERIOD_PRESET:

LOAD *,

if(PeriodCat='LastYTDFlag',YEAR(Date)+1,YEAR(Date))&PICK(WILDMATCH(PeriodCat,'YearMonth','QuarterYear','HalfYear','FullYear','LastYTDFlag','CurYTDFlag'),

  num(Month(Date),'00'),'2'&Ceil(Month(Date)/3),'3'&Ceil(Month(Date)/6),'40','50','60') as PeriodSortOrder,  //periods grouped as Yearmonth , QUARTERS , Half Years, FY, YTDFlags

if(wildmatch(Period,'YTD*'), Year(Date)&7*10,PICK(WILDMATCH(PeriodCat,'YearMonth','QuarterYear','HalfYear','FullYear'),

  YEAR(Date)&Ceil(Month(Date)/3)*10+num(Month(Date),'0')  // Yearmonth

  ,(YEAR(Date)&Ceil(Month(Date)/3)*10)+((Ceil(Month(Date)/3)*3)+1)  // QuarterYear

  ,(YEAR(Date)&3*Ceil(Month(Date)/6)*10) //HalfYEar

  ,YEAR(Date)&8*10)) as Sort_PeriodWise         ///////////Periodwise Sort Order

RESIDENT TEMP_PRESET;

DROP TABLE TEMP_PRESET;

vinieme12
Champion III
Champion III

You will have to create your own period flags based on what you want to see in your pivot table..

Example:

if(Month(TempDate)=Month($(vToday)) , Weekstart(Date)) as CurMnthByWeek,

if(Year(TempDate) = Year($(vToday)) And Ceil(Month(TempDate)/3)=Ceil(Month($(vToday))/3), MonthName(TempDate)&'-'&Year(TempDate)) as CurQTR,

if(Year(TempDate) = Year($(vToday)) And Ceil(Month(TempDate)/3)=Ceil(Month($(vToday))/3)-1, MonthName(TempDate)&'-'&Year(TempDate)) as PrevQTR,

if(Year(TempDate) = Year($(vToday))-1 And Ceil(Month(TempDate)/3)=Ceil(Month($(vToday))/3), MonthName(TempDate)&'-'&Year(TempDate)) as QTRPrevYear,





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