5 Replies Latest reply: Feb 4, 2016 4:39 AM by Sunny Talwar

# Set Analysis on date greater than variable

Below works:

Sum(if(KPI_DATE>=v6MonthsAgo, KPI_SALES, 0))

Below doesn't work:

Sum({<KPI_DATE={">=\$(v6MonthsAgo)"}>} KPI_SALES)

My actual use is Avg so cannot use if (..., ..., ...)

• ###### Re: Set Analysis on date greater than variable

Probably the date format that's giving problems. Try LET v6MonthsAgo = num(Addmonths(Today(),-6));

If that doesn't work then post a small qlikview document that demonstrates the problem.

• ###### Re: Set Analysis on date greater than variable

I try to use Date() and MakeDate() and turn out MakeDate() works but Date() doesn't.  Any idea on this?

Sum({<DATE_FROM_MAKEDATE={">=\$(v6MonthsAgo)"}>} VOLUME) <-- works

Sum({<DATE_FROM_STR={">=\$(v6MonthsAgo)"}>} VOLUME) <-- not work

DATE_TRUNC,

DATE_YEAR,

DATE_MONTH,

DATE_DAY,

DATE_STR,

Date(DATE_STR,'YYYY-MM-DD') as DATE_FROM_STR,

MakeDate(DATE_YEAR, DATE_MONTH, DATE_DAY) as DATE_FROM_MAKEDATE,

INDEX_VOLUME AS VOLUME;

SQL SELECT

INDEX_DATE as INDEX_DATE,

TRUNC(INDEX_DATE) as DATE_TRUNC,

EXTRACT(YEAR FROM INDEX_DATE) DATE_YEAR,

EXTRACT(MONTH FROM INDEX_DATE) DATE_MONTH,

EXTRACT(DAY FROM INDEX_DATE) DATE_DAY,

TO_CHAR(INDEX_DATE, 'YYYY-MM-DD') as DATE_STR,

INDEX_VOLUME

FROM INDEX_TABLE;

• ###### Re: Set Analysis on date greater than variable

Try this with your the expression you are using currently:

DATE_TRUNC,

DATE_YEAR,

DATE_MONTH,

DATE_DAY,

DATE_STR,

Date(DATE_STR) as DATE_FROM_STR,

MakeDate(DATE_YEAR, DATE_MONTH, DATE_DAY) as DATE_FROM_MAKEDATE,

INDEX_VOLUME AS VOLUME;

SQL SELECT

INDEX_DATE as INDEX_DATE,

TRUNC(INDEX_DATE) as DATE_TRUNC,

EXTRACT(YEAR FROM INDEX_DATE) DATE_YEAR,

EXTRACT(MONTH FROM INDEX_DATE) DATE_MONTH,

EXTRACT(DAY FROM INDEX_DATE) DATE_DAY,

TO_CHAR(INDEX_DATE, 'YYYY-MM-DD') as DATE_STR,

INDEX_VOLUME

FROM INDEX_TABLE;

• ###### Re: Set Analysis on date greater than variable

HI,

Try like this

Sum({<KPI_DATE={">=\$(=v6MonthsAgo)"}>} KPI_SALES)

Note: The date format in v6MonthsAgo & KPI_DATE field should be same then only this works.

Regards,

Jagan.

• ###### Re: Set Analysis on date greater than variable

try like this?