topic Re: How to group time ranges in different columns? in QlikView

How to group time ranges in different columns?

Wed, 25 Nov 2020 16:16:04 GMT

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?

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.

Re: How to group time ranges in different columns?

vinieme12 — Tue, 31 Jan 2017 05:40:33 GMT

This should give you a hint how to

Period Presets: Compare Periods on the fly

Re: How to group time ranges in different columns?

Wed, 01 Feb 2017 14:23:27 GMT

I will look into that. Thank you.

Re: How to group time ranges in different columns?

Fri, 10 Feb 2017 15:59:41 GMT

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;

Re: How to group time ranges in different columns?

vinieme12 — Fri, 10 Feb 2017 18:41:52 GMT

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,