Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
This should give you a hint how to
Period Presets: Compare Periods on the fly
I will look into that. Thank you.
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;
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,