topic Problem with IF in the table in QlikView

Problem with IF in the table

Tue, 11 Jan 2011 10:50:29 GMT

I created the following table:

REEL_BUDGET_PREV:

LOAD LIBELLE_GPE_RES_IMP_PP,

LIBELLE_GPE_PROFIT_PERTE_PP,

LIBELLE_GPE_CPT_PP,

LIBELLE_COMPTE_PP,

DATEECR_PP,

DEBIT_PP,

CREDIT_PP,

CPTE_PP,

AMOUNT_PP,

DATBUD_PP

RESIDENT AUXIL;

When I filter compared to DATBUD_PP using the following query

IF (DATBUD_PP> = '01 / 01 / "& Date (Today (), 'YYYY') AND DATBUD_PP <= Date (Today (), 'DD / MM / YYYY'), Sum (AMOUNT_PP))

I have not results. But the dates in the specified interval exists and also amounts.

What may be the problem and how to solve it??

Thanks for your help.

Re. :Problem with IF in the table

martin59 — Tue, 11 Jan 2011 11:17:10 GMT

Hi Yimen,

You can try something like this :

Sum(if(InYearToDate(DATBUD_PP,Today(),0), AMOUNT_PP))

Hope it helps you

Problem with IF in the table

Tue, 11 Jan 2011 17:25:53 GMT

IF (DATBUD_PP> = '01 / 01 / "& Date (Today (), 'YYYY') AND DATBUD_PP <= Date (Today (), 'DD / MM / YYYY'), Sum (AMOUNT_PP))

You are using: '01 / 01 / "& Date (Today (), 'YYYY')

but i think you need '01 / 01 /' & Year (Today ())

hope this help

Re:Problem with IF in the table

Oleg_Troyansky — Tue, 11 Jan 2011 18:44:32 GMT

You care comparing your date field to a string - '01 / 01 / '...

You should be comparing dates to dates or dates to numbers. I don't think comparing dates to strings can work...

Problem with IF in the table

Tue, 11 Jan 2011 19:13:12 GMT

Hi Yimen,

I think your formula should be like this:

sum(IF (DATBUD_PP> = '01 / 01 / "& Date (Today (), 'YYYY') AND DATBUD_PP <= Date (Today (), 'DD / MM / YYYY'), AMOUNT_PP,0))

The IF condition has to be evaluated in every line of data before Qlikview can add the values.

I hope that solves it.

Cheers

Danny

Re. :Problem with IF in the table

Wed, 12 Jan 2011 09:47:13 GMT

Good morning.

I have not found the explanation on the function : InYearToDate(DATBUD_PP,Today(),0)

Please can someone explain me the role of this function?

Thanks for your help.

Re. :Problem with IF in the table

sparur — Wed, 12 Jan 2011 09:57:46 GMT

Hi,

what about built-in help system? Did you use it? 🙂

this is explanation from help system:

InYearToDate ( date, basedate , shift [, first_month_of_year = 1] )

returns true if date lies inside the part of year containing basedate up until and including the last millisecond of basedate. The year can be offset by shift. Shift is an integer, where the value 0 indicates the year which contains basedate. Negative values in shift indicate preceding years and positive values indicate succeeding years. If you want to work with (fiscal) years not starting in January, you may indicate a value between 2 and 12 in first_month_of_year.

Examples:

inyeartodate ( '2006-01-25', '2006-02-01', 0 ) returns true

inyeartodate ( '2006-01-25', '2006-01-01', 0 ) returns false

inyeartodate ( '2005-01-25', '2006-02-01', -1 ) returns true