topic Re: What wrong with this syntax? in QlikView

What wrong with this syntax?

adurgani — Wed, 25 Nov 2020 16:16:04 GMT

I am sure that this is really rudimentary, but what's wrong with this formula?

=Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1}>}[Actuals.FIN]) - Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]=month(today())>}[Actuals.FIN])

I'm adding actuals for the whole year and deducting current month's actuals.

Re: What wrong with this syntax?

Anonymous — Mon, 10 Sep 2018 14:35:38 GMT

check if your field [Month Num] is same Format as month(today())

month(today()) gives back string like "Aug". If [month Num] contains number you should use date(today(),'MM')

Re: What wrong with this syntax?

YoussefBelloum — Mon, 10 Sep 2018 14:43:46 GMT

Hi,

the red part is the wrong part

=Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1}>}[Actuals.FIN]) - Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]=month(today())>}[Actuals.FIN])

try it like this:

=Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1}>}[Actuals.FIN]) - Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]={'$(=month(today()))'}>}[Actuals.FIN])

Re: What wrong with this syntax?

adurgani — Mon, 10 Sep 2018 15:02:59 GMT

Thank you! this really helps

Re: What wrong with this syntax?

adurgani — Tue, 11 Sep 2018 18:23:43 GMT

Continuing to build on this - Now what am I doing wrong? The first part of the formula obviously works, it's the if statement that doesn't seem to work.

If the date is less than 15 deduct the total of current month and previous month if greater than 15 then just current month from the total for the year.

Sum({<[Year]={$(vCurrentYear)},[Major Category]={'Project Costs'},[%RED_IT_FINANCIAL_ALL]={1}>}[Actuals.FIN])

- if(date('$(today())','DD')>15,Sum({<[Year]={$(vCurrentYear)},[Major Category]={'Project Costs'},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]={'$(=month(today())+0)'}>}[Actuals.FIN]),

Sum({<[Year]={$(vCurrentYear)},[Major Category]={'Project Costs'},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]={'$(=month(today())+0)'}>}[Actuals.FIN]) + Sum({<[Year]={$(vCurrentYear)},[Major Category]={'Project Costs'},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]={'$(=month(today())+1)'}>}[Actuals.FIN]))