Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

problem with date in an expression

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

9 Replies
MVP
MVP

problem with date in an expression

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

Not applicable

Re: problem with date in an expression

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

MVP
MVP

problem with date in an expression

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

Hi,

     Have you tried with date function.

    

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

Celambarasan

Not applicable

Re: problem with date in an expression

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

Not applicable

Re: problem with date in an expression

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

MVP
MVP

problem with date in an expression

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

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

Not applicable

Re: problem with date in an expression

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