problem with date in an expression

[#1]

Hi everyone,

     i have this expression witch run well

sum( {$<CALENDARIO_PLAN_DEALS.AñoMes = {'$(Mes_Actual)'}>} PLAN_DEALS$.Deals.plan )

but when i add the symbol "<" the expression crash

sum( {$<CALENDARIO_PLAN_DEALS.AñoMes <= {'$(Mes_Actual)'}>} PLAN_DEALS$.Deals.plan )

any ideas?

Thanks

Gerardo

[swuehl]

You need to see the set expression more like an assignment, not like a comparison. So a <= doesn't make sense in  an assignment kind of sense. It's not an allowed operator here.

If you need to select all values in CALENDARIO_PLAN_DEALS.AnoMes that are smaller equal, you need to use a search expression in your set element:

sum( {$<CALENDARIO_PLAN_DEALS.AñoMes = {"<=$(Mes_Actual)"}>} PLAN_DEALS$.Deals.plan )

Note the double quotes here.

Regards,

Stefan

[#3]

Hi Stefan, thanks for help me again…..

I tried with the expression you give me but the value of the result is 0.

In the picture im going to attach you can see values for all months.

The value for Mes_Actual is “Mar-2012”

Any suggestion?

Thanks

gerardo

De: swuehl

Enviado el: jueves, 08 de marzo de 2012 03:59 p.m.

Para: Pauza, Gerardo

Asunto: - Re: problem with date in an expression

QlikCommunity <http://community.qlik.com/index.jspa>

Re: problem with date in an expression

created by swuehl <http://community.qlik.com/people/swuehl> in New to QlikView - View the full discussion <http://community.qlik.com/message/199216#199216

[swuehl]

How do you create your Mes_Actual? Often the date format is kind of problematic in set expressions or comparisons. It might be better to use a numerical date format instead of the text representation.

[CELAMBARASAN]

Hi,

     Have you tried with date function.

sum( {$<CALENDARIO_PLAN_DEALS.AñoMes = {"<=$(=Date($(Mes_Actual)))"}>}PLAN_DEALS$.Deals.plan )

Celambarasan

[#6]

Hi Stefan,

This is the way i create the variable

LET Mes_Actual= Month(today()) & '-' & year(today());

I don’t if is this correct..

Thanks

gerardo

De: swuehl

Enviado el: jueves, 08 de marzo de 2012 09:39 p.m.

Para: Pauza, Gerardo

Asunto: - Re: problem with date in an expression

QlikCommunity <http://community.qlik.com/index.jspa>

Re: problem with date in an expression

created by swuehl <http://community.qlik.com/people/swuehl> in New to QlikView - View the full discussion <http://community.qlik.com/message/199284#199284

[#7]

Hi Celambarasan,

I tried but the value is 0 too. I think this is because the way i created the variable Mes_Actual, like said Stefan.

Thanks.

Gerardo

De: Celambarasan Adhimulam

Enviado el: viernes, 09 de marzo de 2012 01:37 a.m.

Para: Pauza, Gerardo

Asunto: - Re: problem with date in an expression

QlikCommunity <http://community.qlik.com/index.jspa>

Re: problem with date in an expression

created by Celambarasan Adhimulam <http://community.qlik.com/people/celambarasan> in New to QlikView - View the full discussion <http://community.qlik.com/message/199269#199269

[swuehl]

I would recommend creating your AnoMes as a date type using either Monthname

LOAD

...

Fecha,

MonthName(Fecha) as AnoMes,

..

from...

or monthstart()

LOAD

...

Fecha,

Date(Monthstart(Fecha),'MMM-YYYY') as AnoMes,

..

from...

Then create your Variable Mes_Actual in the same way using today() instead of Fecha.

For use in the set expression, I think it's a good idea to use the numerical representation

LET Mes_Actual_Num = num(monthstart(today()));

Then this should work:

sum( {$<CALENDARIO_PLAN_DEALS.AñoMes = {"<=$(Mes_Actual_Num)"}>} PLAN_DEALS$.Deals.plan )

Hope this helps,

Stefan

[CELAMBARASAN]

Hi,

     Check with this

sum( {$<CALENDARIO_PLAN_DEALS.AñoMes ={"<=$(=Date#($(Mes_Actual),'MMM-YYYY'))"}>}PLAN_DEALS$.Deals.plan )

Or

Simply use

sum( {$<CALENDARIO_PLAN_DEALS.AñoMes ={"<=$(=Today())"}>}PLAN_DEALS$.Deals.plan )

Celambarasan

[#10]

Finaly the expression for “plan” is working with your help!

To be clear im going to write all expressions i used for

Plan expression:

sum( {$<PLAN_DEALS$.Fecha = {"<=$(Mes_Actual_Num)"}>} PLAN_DEALS$.Deals.plan )

Mes_Actual_Num:

LET Mes_Actual_Num = num(monthstart(today()))

e.g: 40696

PLAN_DEALS$.Fecha

e.g: 40696-40940-40909-……

so i used numeric values instead of ‘MM-YYYY’ format in dates.

I tried this to: sum( {$<CALENDARIO_PLAN_DEALS.AñoMes ={"<=$(=Date#($(Mes_Actual),'MMM-YYYY'))"}>}PLAN_DEALS$.Deals.plan )

But i think not found because of the format in dates.

Thank a lot.

Gerardo

De: swuehl

Enviado el: viernes, 09 de marzo de 2012 11:21 a.m.

Para: Pauza, Gerardo

Asunto: - Re: problem with date in an expression

QlikCommunity <http://community.qlik.com/index.jspa>

Re: problem with date in an expression

created by swuehl <http://community.qlik.com/people/swuehl> in New to QlikView - View the full discussion <http://community.qlik.com/message/199506#199506