Help with dimension calculated

[#1]

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.

[senpradip007]

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')

[#3]

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.

[senpradip007]

Could you please upload sample qvw?

[PrashantSangle]

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

like

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

[#6]

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

[PrashantSangle]

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

like

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

[sujeetsingh]

I will recommend you to do this in SCRIPT.

[#9]

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.

[PrashantSangle · Accepted solution]

Hi,

If you have already that field then its more simpler.

Try in chart

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

Regards