Problem with IF in the table

[#1]

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.

[martin59]

Hi Yimen,

You can try something like this :

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

Hope it helps you

[#3]

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

[Oleg_Troyansky]

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

[#5]

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

[#6]

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.

[sparur]

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