topic Re: Set analysis expression in QlikView

Set analysis expression

clausbarlose — Wed, 16 Jul 2014 13:53:33 GMT

Hope anyone is able to help me with this set analysis question.

I have a data record, consisting of:

Ticket ID

Creation date

Resolve date

…

- and want a bar chart to show year/mont, e.g. 2014/03, on x-axis and a stacked bar showing how many tickets created the specific month and how many that are resolved the same month.

I might have selected January ’13 to January ’14 in my dataset, but off course wants to look at tickets created before selection, but resolved in selection period.

Obviously it's the expression that kills me...

Thanks to anyone that may help me!

Best
regards,

Claus

Re: Set analysis expression

VishalWaghole — Thu, 17 Jul 2014 05:06:09 GMT

Hi Claus,

Try this one

Edit Script logic :

Load year(Date) &'/'& num(month(Date)) as year/month,

if(isNull(ResolveDate),NULL,NOTNULL) as RDateFlag

From Datasource;

Expressions used in charts :

Dimension -- year/month

Expression 1 -- count(TicketID)

Expression 2 -- count({<RDateFlag = {'NOTNULL'}>}TicketID)

-- Regards,

Vishal Waghole

Re: Set analysis expression

clausbarlose — Thu, 17 Jul 2014 07:42:49 GMT

Hi Vishal

I'll try it later today! Thank you very much!

Kind regards,

Claus

Re: Set analysis expression

clausbarlose — Thu, 17 Jul 2014 18:23:06 GMT

Hi Vishal

This doesn't seem to do the job...

Tickets doesn't necessarily get solved the same month they are created. Some might take several months to solve. E.g.:

Ticket ID, Created, Resolved

100, 2013-05-05, 2014-07-10

101, 2013-05-.06, 2013-06-01

102, 2013-05-07, 2013-05-07

...

Kind regards,

Claus

Re: Set analysis expression

gmoraleswit — Thu, 17 Jul 2014 19:20:04 GMT

Hi Claus,

Here is one way to do it, it involves creating one record for the created date and one for the resolved date for each ticket, see attach

Hope this helps

Re: Set analysis expression

clausbarlose — Thu, 17 Jul 2014 21:02:08 GMT

Hi Gabriela

Thank you very much, it does the job

But can't it be done through set analysis? From a dimension to count values alike on another field in the records?

Kind regards,

Claus

Re: Set analysis expression

gmoraleswit — Fri, 18 Jul 2014 13:27:44 GMT

Can you send an example of your data model? It can be done, but it depends on how you link your calendar with the table. You can do something like this:

count({$<CreatedMonthYear=P(MonthYear), MonthYear=>} ticketID)

In this case MonthYear is the field where you select the values

Re: Re: Set analysis expression

clausbarlose — Fri, 18 Jul 2014 19:40:20 GMT

Record is pretty small - it's a ticket database from a serviceprovider. No special need for calendar implementation as all violations and such is calculated in source system.

Please look at the attached for inspiration. - I am very satisfied with the solution you provided for me, so please don't feel obligated to finding a set analyses solution but for the challenge of it.

Goal is to find service providers ability to close tickets rather than users create them.

Thank you!

/Claus