topic Can someone help me with this expression? in QlikView

Can someone help me with this expression?

Tue, 26 Jul 2011 14:32:33 GMT

Hello Experts,

I have 2 variables, HistoryDateFrom and HistoryDateTo, which store values from a Date field called Effective_Start. The problem is when I check the variable in a text object it returns as number instead of date and I know in the script I am converting this to date...

In a text box if I do =$(vHistoryDateFrom) I Get 40675

But when I do = date($(vHistoryDateFrom)) I Get 15/11/2010

So to hardcode it I am writing a below expression which works on some other application and not here.

=count ({<Effective_start = {'<=date($(=vHistoryDateTo))'},Effective_end = {'>=date($(=vHistoryDateFrom))'}>}distinct (dim_opportunity.crm_opportunity_id & dim_opportunity.Status))

Can anyone help me to convert my variables in this expression to date format and then calculate??/

Thanks in advance

Mady

Anyone please??

Can someone help me with this expression?

Tue, 26 Jul 2011 15:24:50 GMT

Hi,

what i understood from you that you want to count field values between start and end date and these dates are in variables

That's True??

Can someone help me with this expression?

Tue, 26 Jul 2011 15:30:17 GMT

True, and i have given the situation clearly as well, what I have. Again I want to count something between the selected dates, which show as numbers and I think thats casuing the calculation problems... But when I wrap those variables with Date() they show the correct dates, now i want to use this similar function with Set Analysis expression.

Thanks Again

Can someone help me with this expression?

deepakk — Tue, 26 Jul 2011 15:31:08 GMT

hi,

You can work it in below way.

create a number field for Date Effective_start and Effective_end and use it in your expression

count ({<Effective_start_num = {'<=($(=vHistoryDateTo))'},Effective_end_num = {'>=($(=vHistoryDateFrom))'}>}distinct (dim_opportunity.crm_opportunity_id & dim_opportunity.Status))

This will sort out your issue.

Can someone help me with this expression?

Tue, 26 Jul 2011 15:35:00 GMT

Hello Deepak,

I am unsure what you meant by asking me to create a num field for variables?

Now I have my variables as Calendar Objects, where the end user can select dates. What do you want me to try?

Can you please explain a bit in detail.

Thank again for your time.

Mady

Can someone help me with this expression?

Tue, 26 Jul 2011 15:37:57 GMT

Simple:

use date#() instead of date()

Can someone help me with this expression?

deepakk — Tue, 26 Jul 2011 15:42:22 GMT

hi,

Since you are getting Numberic data from your calendar objects, Create a Number date field at script level like

Num(Effective_start) as Effective_start_num,

Num(Effective_end) as Effective_end_num

This will make it easy set analysis caluclation

count ({<Effective_start_num = {'<=($(=vHistoryDateTo))'},Effective_end_num = {'>=($(=vHistoryDateFrom))'}>}distinct (dim_opportunity.crm_opportunity_id & dim_opportunity.Status))

I hope you got my point.

Deepak

Can someone help me with this expression?

Tue, 26 Jul 2011 15:43:04 GMT

Hello , do you want me to use Date# for the below expression, as I tried for a text box around the variable and it doesnt work?

Can you explain more please?

=count ({<Effective_start = {'<=date($(=vHistoryDateTo))'},Effective_end = {'>=date($(=vHistoryDateFrom))'}>}distinct (dim_opportunity.crm_opportunity_id & dim_opportunity.Status))

Can someone help me with this expression?

Tue, 26 Jul 2011 15:59:48 GMT

Hello Deepak,

This worked as its hardcoded now, but what do you think was the problem converting the effective_start field to date and using it? I have used it for another app and it worked exactly as I wanted but here I have to stick to the numbers?

Can you explain a bit more as to if I have this effective start date linked to any other date which is in number format then would that cause a problem to this?? and also by using the num() can we trust the data what we see is correct?

Thanks,

Mady

Can someone help me with this expression?

deepakk — Tue, 26 Jul 2011 16:58:45 GMT

hi Mady,

Yes you can trust num and since its a numeric figure , filteration will be slightly faster.

You issue might be pattern of date format. Check the format of the date. for the variables and date field.

I alsways prefer numeric date field as it is easy to manage.