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: 
adurgani
Contributor III
Contributor III

What wrong with this syntax?

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.

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

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])

View solution in original post

4 Replies
Anonymous
Not applicable

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')

YoussefBelloum
Champion
Champion

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])

adurgani
Contributor III
Contributor III
Author

Thank you! this really helps

adurgani
Contributor III
Contributor III
Author

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]))