Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis on date greater than variable

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

5 Replies
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.


talk is cheap, supply exceeds demand
Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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
Not applicable
Author

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;