topic Re: Dynamic calculation for past months data in QlikView

Dynamic calculation for past months data

Mon, 02 Feb 2015 10:30:18 GMT

I want to calculate the percentage of tickets that are open at the end of every month.

Formula is : % open = count of tickets open at end of month/count of tickets submitted in that month.

BUT the challenge arises when the tickets are resolved and I want to show the open tickets for older months.

For instance if I were to look/calculate for a particular month in the past now, say November 2014;

I will count all the tickets with "Submit Date" < November but with Resolved Date > November,2014 or empty (ie. still open in February). Once I get this I can divide this by the count of tickets submitted in November to get the percentage I'm looking for.

Fields are :

SubmitDate
ResolvedDate (will be empty for still open tickets)
IncidentID

Could someone please help me set this expression on my chart?

Best Regards,

Tony

Re: Dynamic calculation for past months data

robert_mika — Mon, 02 Feb 2015 11:00:43 GMT

Could you attached your file?

Excel or qvq

Re: Dynamic calculation for past months data

Mon, 02 Feb 2015 11:56:52 GMT

Hi Robert,

Thanks for the reply. Please find attached file as requested, I have added only the relevant fields to cut down on the size.

Also I'm including the expected chart as i make in Excel for your reference please.

Hoping to hear on your solution.

Best Regards,

Tony

Re: Dynamic calculation for past months data

hic — Wed, 04 Feb 2015 13:09:13 GMT

I would solve this in the data model, by generating all months between the submission and the resolution.

Incidents:

LOAD

[Submit Date],

[Last Resolved Date],

Floor([Last Resolved Date] - [Submit Date]) as DaysOpen,

[Service Line],

[Incident Number]

FROM [Raw_Sample_for QV Blog v4.xlsx] (ooxml, embedded labels, table is Sheet3);

Months: // Months between the submit date and resolved date

Load

MonthEnd(AddMonths(MonthStart([Submit Date]),IterNo()-1)) < [Last Resolved Date] as Open,

IterNo() = 1 as IsSubmissionMonth,

Date(AddMonths(MonthStart([Submit Date]),IterNo()-1),'YYYY MMM') as Month,

[Incident Number]

Resident Incidents

While MonthEnd(AddMonths(MonthStart([Submit Date]),IterNo()-1)) <= MonthEnd([Last Resolved Date]) ;

With the above script, you can use Month as dimension and the following two expressions to calculate the number of open cases and the number of submitted cases:

* Count ({$<Open={'-1'}>} distinct [Incident Number])

* Count ({$<IsSubmissionMonth={'-1'}>} distinct [Incident Number])

However, my numbers are different from your graph, so you should check them...

HIC

Re: Dynamic calculation for past months data

Fri, 06 Feb 2015 11:04:13 GMT

Hi Henric,

Thanks for this amazing reply. It took me some time to understand the code. Everything is working good but in my raw data provided earlier, I had stripped some data to reduce the size, hence I did not supply the tail end of the data where "Last Resolved Date" is blank since it is unresolved and still open.

As a result the code is not capturing those tickets where "Last Resolved Date" is blank. This is because the iteration for While function is counting only till the <= MonthEnd([Last Resolved Date]) (?)

I did try using my logic for a workaround, but failed.

Could you please help me with this little niggle?

Waiting with anticipation.

Best Regards,

Tony

Re: Dynamic calculation for past months data

hic — Fri, 06 Feb 2015 11:18:39 GMT

You need to replace a blank [Last Resolved Date] with Today() in some of the expressions. I would do the following (but I cannot test it since I don't have source data, so beware of bugs...) This way, also incidents that are still open will have an end date for the range: Today.

Leave the first table as it is. Change the second table to:

Months:

Load

MonthEnd(AddMonths(MonthStart([Submit Date]),IterNo()-1)) < RangeMin( Today() +1, [Last Resolved Date] ) as Open,

IterNo() = 1 as IsSubmissionMonth,

Date(AddMonths(MonthStart([Submit Date]),IterNo()-1),'YYYY MMM') as Month,

[Incident Number]

Resident Incidents

While MonthEnd(AddMonths(MonthStart([Submit Date]),IterNo()-1)) <= RangeMin( Today(), MonthEnd([Last Resolved Date]) ) ;

HIC

Re: Dynamic calculation for past months data

Mon, 09 Feb 2015 12:31:21 GMT

Hi Henric,

Smart and wonderful workaround. The logic is working just fine.

Thanks a ton,

Best Regards,

Tony

Re: Dynamic calculation for past months data

Wed, 11 Feb 2015 10:30:42 GMT

Hi Henric,

Continuing from here, I have doubts regarding how to show the chart as per certain condensations. Could you please have a look into Dynamic Chart with conditional showcase of months and come up with your expert advice?

As always awaiting with anticipation.

Best Regards,

Tony