Bar chart with start date

[#1]

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

[sunny_talwar]

May be try this expression:

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

[#3]

Hi Sunny,

This change unfortunately does not work.

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

[sunny_talwar]

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

[#5]

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;

[Anonymous]

can you share your sample file

[#7]

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

[sunny_talwar]

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)

[shree909 · Accepted solution]

Try this

Add Dimension as:

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

Add Expression As

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

[#10]

Thank you so much,

Changing the dimension worked!