topic Re: Help with dimension calculated in QlikView

Help with dimension calculated

Mon, 09 Feb 2015 04:05:22 GMT

Good day.

I need showing in a table the total days leading open support tickets asset.

For this I have in my database a field called "date of creation" (format: 01/30/2015 11:51:42 am) and a field called "State", I need to subtract the [creation date - current date ] of tickets with [state] = 'active' to show how long they open current ticket.

I can not create the Dimension calculated to perform this calculation.

Greetings.

Re: Help with dimension calculated

senpradip007 — Mon, 09 Feb 2015 04:15:18 GMT

Use this

=Timestamp(Now(), 'MM/DD/YYYY h:mm:ss TT')

Timestamp#('01/30/2015 11:51:42 am', 'MM/DD/YYYY h:mm:ss TT')

Re: Help with dimension calculated

Mon, 09 Feb 2015 04:27:30 GMT

Thanks Pradip Sen, I tried using the expression, but it does not calculate anything, it will make the filter [State] = 'Active'

The expression to use is:

= Timestamp (Now (), 'MM / DD / YYYY h: mm: ss TT')

Timestamp # ([Date Creation], 'MM / DD / YYYY h: mm: ss TT')

Thanks for you help.

Regards.

Re: Help with dimension calculated

senpradip007 — Mon, 09 Feb 2015 04:33:07 GMT

Could you please upload sample qvw?

Re: Help with dimension calculated

PrashantSangle — Mon, 09 Feb 2015 04:49:22 GMT

Hi,

Considering your date creation field in date

then use Interval()

try like,

Interval(today()-[Date Creation],'D hh:mm')

If [Date Creation] field not in date then

use timestamp#() to convert it into date

Interval(today()-timestamp#([Date Creation],'MM/DD/YYYY h:mm:ss TT'),'D hh:mm')

If you want to compare with current date and time then replace Today() with now()

Regards

Re: Help with dimension calculated

Mon, 09 Feb 2015 05:00:27 GMT

Thanks Dreamer Max, the expression works, now we just need the table shows only open tickets, using SET analisys.

Any recommendation?

Thank for you help

Re: Help with dimension calculated

PrashantSangle — Mon, 09 Feb 2015 05:16:28 GMT

Hi,

Create flag in backend

use above condition to create flag

Try like

if(Interval(today()-timestamp#([Date Creation],'MM/DD/YYYY h:mm:ss TT'),'D hh:mm')>0,'Open','Close') as Open_Close

Then use flag field in your chart

if(Open_Close='Open',Interval(today()-timestamp#([Date Creation],'MM/DD/YYYY h:mm:ss TT'),'D hh:mm'),0)

In Front End

try like

If(Interval(today()-timestamp#([Date Creation],'MM/DD/YYYY h:mm:ss TT'),'D hh:mm')>0,Interval(today()-timestamp#([Date Creation],'MM/DD/YYYY h:mm:ss TT'),'D hh:mm'),0)

Better if you do this complex calculation at script level.

Regards

Re: Help with dimension calculated

sujeetsingh — Mon, 09 Feb 2015 05:20:01 GMT

I will recommend you to do this in SCRIPT.

Re: Help with dimension calculated

Mon, 09 Feb 2015 05:32:18 GMT

Thanks Max Dreamer, analyzing your proposal, your RECOMMEND make use of a flag, but I think it would not be necessary, because I have a field in my BD indicating whether the ticket is open or closed [State]. I am new to QlikView and know little of Set Analisys.

My logic tells me I need to do the following

= Now() - [Date_Created] where [Estatus]='Active';

Just do not know how to express it with Set Analysis within my expression in the graph.

Thaks.

Re: Help with dimension calculated

PrashantSangle — Mon, 09 Feb 2015 05:37:51 GMT

Hi,

If you have already that field then its more simpler.

Try in chart

if([Estatus]='Active',Expression)

Regards

Re: Help with dimension calculated

Mon, 09 Feb 2015 16:20:59 GMT

Thanks Max Dreamer for your support, yet still displays all data without applying the filter, I see that now only does the calculation for all tickets with status = 'Active' and resolved that only show data actives using triggers and markers.

The expression remained well:

if ([Status] = 'Active', Interval (today () - [Date Creation], 'D'))

Thanks for the support, the solution helped me and have adapted to cover all my needs.

Greetings.