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

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

Thu, 23 Apr 2015 18:22:45 GMT

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

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

sunny_talwar — Thu, 23 Apr 2015 18:42:12 GMT

You can do something like this:

If(IsNull(FieldName), valueYouWantToSpecify, FieldName)

HTH

Best,

Sunny

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

Thu, 23 Apr 2015 19:51:38 GMT

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

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

sunny_talwar — Thu, 23 Apr 2015 19:53:19 GMT

I don't see anything attached.

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

Thu, 23 Apr 2015 19:58:53 GMT

sunindia‌

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

sunny_talwar — Thu, 23 Apr 2015 20:55:32 GMT

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

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

Thu, 23 Apr 2015 21:10:17 GMT

yes sunny.

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

petter — Thu, 23 Apr 2015 21:50:26 GMT

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.

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

Fri, 24 Apr 2015 20:39:19 GMT

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

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

Tue, 28 Apr 2015 23:05:14 GMT

Hi Petter,

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

- sb