How to show some value where there are no data/tickets for the service?

[#1]

Hi,

I have a scenario which is, I have business service field and each business service will have tickets for each month, but in any case any of the business service does not have any tickets at all or for some months. In this case I will have to show 100 or some value and I should not use any external file (excel) or cannot do any changed in database (source side).

Can any please share your ideas or solution that how to achieve in QlikView itself?

Thanks

sb

[sunny_talwar]

You can do something like this:

If(IsNull(FieldName), valueYouWantToSpecify, FieldName)

HTH

Best,

Sunny

[#3]

Hi Sunny,

I did the same but the results are partial.

Plz see the attached results and tickets which are in particular month for that service should show value. Also when we click on one business service for that service current month to till the specified month the given value should display.

- sb

[sunny_talwar]

I don't see anything attached.

[#5]

sunindia‌

[sunny_talwar]

I am not sure I understand your question? Are you saying that if a Business Service don't have a Target given for a certain month (row doesn't exist), you want to create one for them???

Best,

Sunny

[#7]

yes sunny.

[petter · Accepted solution]

I guess this might be interesting for you - how to generate rows for dates that are missing for any business service:

It is lines 50 to 59 that goes through what is loaded from an external source and checks if there are any ticket at any particular date for every business service. If it is no tickets for a business service for a particular date a 0 tickets is generated for that date.

[#9]

Thanks Petter.

Hope I am not asking more:)

Source Data:

Table: CI_Service (QVD)

Business Service

Target_sla

Table: Incident (QVD)

Business Service

Incident

start_date

end_date

Reports Needed:

1). Dim: Business Service  Measure: Attainment %

Formula of Attaintment %:

(100-if(isnull(100 * ((end dt- start dt)/TotalAvailableHours)),0,(100 * ((end dt - start dt)/TotalAvailableHours))))

Note: totalAvaialablehouse is the max number of dates (ex: 30 or 31) in the month * 24

2). Current Fiscal Business Attainment Report

Dim: Month (Feb, March April etc.. (note: our fiscal year month start with Feb)

Measure: Attainment %

3). Previous Fiscal Year (4 QTRs) Business Attainment Report

Dim: Fiscal QTR

Measure: Attainment %

Note: main problem occurring in report 2 and 3 because of the no incidents/tickets are no there in some months for some business services.

Note: I have to show 100% attainment where there are no business service incidents for the month/date.

Thanks

SB

[#10]

Hi Petter,

thanks for the logic and I am able to do it:)

- sb