Set analysis expression

[clausbarlose]

Hi

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

[VishalWaghole]

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

[clausbarlose]

Hi Vishal

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

Kind regards,

Claus

[clausbarlose]

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

[gmoraleswit · Accepted solution]

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

[clausbarlose]

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

[gmoraleswit]

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

[clausbarlose]

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