Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I'm pretty new to Qlikview.
I've created a bar chart of the number of ETOPS downgrades per month and year in Qlikview.
I've deselected the suppress zero-values as I want to show the null values in between events.
Now it results as indicated in the linked picture;
I want to only show values greater than Jan-2013.
As dimension I have: CalendarMonthAndYear
As expression I have: =count(if(limitation_type='ETOPS', event_perfno_i))
Can somebody please assist?
Thank you
Try this
Add Dimension as:
If (CalendarDate>='01/01/2013',CalendarMonthAndYear,null())
Add Expression As
=Count({<limitation_type = {'ETOPS'}>} event_perfno_i)
May be try this expression:
=Count({<limitation_type = {'ETOPS'}, CalendarMonthAndYear = {"$(='>=' & Date(MakeDate(2013, 1, 1), 'MMM-YYYY'))"}>} event_perfno_i)
Hi Sunny,
This change unfortunately does not work.
Qlikview accepts the expression, however the data shown is now null:
Is your CalendarMonthAndYear a date field with dual value? Can you show the script where this field is created?
these are the master calendar values
//------------------------------------------------------------------------
// Master Calendar
//------------------------------------------------------------------------
MasterCalendar:
LOAD
TempDate AS CalendarDate,
DAY(TempDate) AS CalendarDay,
WEEKDAY(TempDate) AS CalendarWeekDay,
WEEK(TempDate) AS CalendarWeek,
MONTH(TempDate) AS CalendarMonth,
DAY(TempDate) &'-'& num(MONTH(TempDate)) AS CalendarDayMonth,
YEAR(TempDate) AS CalendarYear,
'Q' & CEIL(MONTH(TempDate)/3) AS CalendarQuarter,
'Q' & CEIL(MONTH(TempDate)/3) & '-' & YEAR(TempDate) AS CalendarQuarterYear,
WEEKDAY(TempDate) & '-' & YEAR(TempDate) AS CalendarWeekDayAndYear,
WEEK(TempDate) & '-' & YEAR(TempDate) AS CalendarWeekAndYear,
MONTH(TempDate) & '-' & YEAR(TempDate) AS CalendarMonthAndYear,
if($(vStartBudgetYear) = 1, YEAR(TempDate) ,if(num(MONTH(TempDate)) >= $(vStartBudgetYear), YEAR(TempDate) & '-' & (YEAR(TempDate)+1), (YEAR(TempDate)-1) & '-' & YEAR(TempDate))) AS BudgetYear
RESIDENT TempCalendar;
can you share your sample file
I don't know if i'm allowed to share the sample file as I am currently working with airline information.
However the event_perfno_i is received from this script.
workorders:
LOAD DISTINCT ac_registr,
ata_chapter,
LEFT(ata_chapter, 2) AS ata_no,
if(closing_date <> 'closing_date',date#(closing_date, 'DD.MMM.YYYY')) AS wo_closing_date,
if(closing_date <> 'closing_date',date#(closing_date, 'DD.MMM.YYYY')) AS CalendarDate,
if(created_date <> 'created_date' , date#(created_date, 'DD.MMM.YYYY')) AS wo_created_date,
if(issue_date <> 'created_date' , date#(issue_date, 'DD.MMM.YYYY')) AS wo_issue_date,
PurgeChar(event_perfno_i,chr(39)) AS event_perfno_i,
comp_partno AS wo_partno,
prio AS wo_prio,
timestamp(timestamp#(left(release_time,2)*60+right(release_time,2), 'mm'), 'hh:mm.ss') AS release_time,
release_sign AS Workorder_release,
type AS workorder_type,
state AS Workorder_state,
date#(mutation, 'DD.MMM.YYYY') AS wo_mutation,
mutation_time,
mel_code,
hil;
SQL SELECT
ac_registr,
ata_chapter,
closing_date,
issue_date,
created_date,
event_perfno_i,
comp_partno,
release_time,
prio,
release_sign,
type,
state,
mutation,
mutation_time,
mel_code,
hil
FROM $(vSource).$(vWOHeaderC) WHERE event_perfno_i <> 'event_perfno_i' AND type <> 'PD';
Can you change the following:
MasterCalendar:
LOAD
TempDate AS CalendarDate,
DAY(TempDate) AS CalendarDay,
WEEKDAY(TempDate) AS CalendarWeekDay,
WEEK(TempDate) AS CalendarWeek,
MONTH(TempDate) AS CalendarMonth,
DAY(TempDate) &'-'& num(MONTH(TempDate)) AS CalendarDayMonth,
YEAR(TempDate) AS CalendarYear,
'Q' & CEIL(MONTH(TempDate)/3) AS CalendarQuarter,
'Q' & CEIL(MONTH(TempDate)/3) & '-' & YEAR(TempDate) AS CalendarQuarterYear,
WEEKDAY(TempDate) & '-' & YEAR(TempDate) AS CalendarWeekDayAndYear,
WEEK(TempDate) & '-' & YEAR(TempDate) AS CalendarWeekAndYear,
Date(MonthStart(TempDate), 'MMM-YYYY') as CalendarMonthAndYear,
if($(vStartBudgetYear) = 1, YEAR(TempDate) ,if(num(MONTH(TempDate)) >= $(vStartBudgetYear), YEAR(TempDate) & '-' & (YEAR(TempDate)+1), (YEAR(TempDate)-1) & '-' & YEAR(TempDate))) AS BudgetYear
RESIDENT TempCalendar;
and then try this expression:
=Count({<limitation_type = {'ETOPS'}, CalendarMonthAndYear = {"$(='>=' & Date(MakeDate(2013, 1, 1), 'MMM-YYYY'))"}>} event_perfno_i)
Try this
Add Dimension as:
If (CalendarDate>='01/01/2013',CalendarMonthAndYear,null())
Add Expression As
=Count({<limitation_type = {'ETOPS'}>} event_perfno_i)
Thank you so much,
Changing the dimension worked!