topic Bar chart with start date in QlikView

Bar chart with start date

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Bar chart with start date

sunny_talwar — Wed, 27 Jul 2016 10:14:25 GMT

May be try this expression:

=Count({<limitation_type = {'ETOPS'}, CalendarMonthAndYear = {"$(='>=' & Date(MakeDate(2013, 1, 1), 'MMM-YYYY'))"}>} event_perfno_i)

Re: Bar chart with start date

Wed, 27 Jul 2016 10:18:37 GMT

Hi Sunny,

This change unfortunately does not work.

Qlikview accepts the expression, however the data shown is now null:

Re: Bar chart with start date

sunny_talwar — Wed, 27 Jul 2016 13:15:43 GMT

Is your CalendarMonthAndYear a date field with dual value? Can you show the script where this field is created?

Re: Bar chart with start date

Wed, 27 Jul 2016 13:18:31 GMT

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;

Re: Bar chart with start date

Anonymous — Wed, 27 Jul 2016 13:18:48 GMT

can you share your sample file

Re: Bar chart with start date

Wed, 27 Jul 2016 13:26:41 GMT

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';

Re: Bar chart with start date

sunny_talwar — Wed, 27 Jul 2016 14:28:29 GMT

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)

Re: Bar chart with start date

shree909 — Wed, 27 Jul 2016 14:30:19 GMT

Try this

Add Dimension as:

If (CalendarDate>='01/01/2013',CalendarMonthAndYear,null())

Add Expression As

=Count({<limitation_type = {'ETOPS'}>} event_perfno_i)

Re: Bar chart with start date

Wed, 27 Jul 2016 14:32:32 GMT

Thank you so much,

Changing the dimension worked!