Set Analysis on date greater than variable

[#1]

I search some similar questions but the solution doesn't work in my case, please help

LET v6MonthsAgo = Date(Addmonths(Today(),-6));

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 (..., ..., ...)

[Gysbert_Wassenaar]

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.

[#3]

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

LOAD INDEX_DATE,

    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;

[jagan]

HI,

Try like this

LET v6MonthsAgo = Date(Addmonths(Today(),-6));

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

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

Regards,

Jagan.

[Anonymous]

try like this?

LET v6MonthsAgo = Date(Addmonths(Today(),-6));

=Sum({<KPI_DATE=, KPI_DATE={'>=$(=v6MonthsAgo)'}>} KPI_SALES)

Make Sure Format of v6MonthsAgo & KPI_DATE should be same like 'DD-MM-YYYY'

[sunny_talwar]

Try this with your the expression you are using currently:

LOAD INDEX_DATE,

    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;