topic problem with date in an expression in QlikView

problem with date in an expression

Thu, 08 Mar 2012 18:46:16 GMT

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

problem with date in an expression

swuehl — Thu, 08 Mar 2012 18:59:01 GMT

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

Re: problem with date in an expression

Thu, 08 Mar 2012 19:25:04 GMT

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

problem with date in an expression

swuehl — Fri, 09 Mar 2012 00:38:45 GMT

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.

problem with date in an expression

CELAMBARASAN — Fri, 09 Mar 2012 04:36:28 GMT

Hi,

Have you tried with date function.

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

Celambarasan

Re: problem with date in an expression

Fri, 09 Mar 2012 13:52:15 GMT

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

Re: problem with date in an expression

Fri, 09 Mar 2012 13:53:27 GMT

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

problem with date in an expression

swuehl — Fri, 09 Mar 2012 14:20:51 GMT

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

Re: problem with date in an expression

CELAMBARASAN — Fri, 09 Mar 2012 14:35:59 GMT

Hi,

Check with this

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

Simply use

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

Celambarasan

Re: problem with date in an expression

Fri, 09 Mar 2012 15:24:52 GMT

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