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: 
Anonymous
Not applicable

sum two sum function

hey I am using this function in my table:

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)

+

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)

and I cant get the result as expected, it only show the value of this expression (the last one): SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)

what am I doing wrong?

19 Replies
sushil353
Master II
Master II

What are you trying to do with

"date(YearStart(MAX(DATEPERIOD))-31)" ?

Anonymous
Not applicable
Author

I want to get the amount from december 2015.

the range of my date period is december 2015 - December 2016

what I am trying to do with the whole formula is to find the average of amount last year and amount date selected.

i.e. the selected month is July 2016 then the average is: (amount as per Dec 2015 + amount as per July 2016)/2

Anonymous
Not applicable
Author

if i run the sum individually, it works.

but when i add it together it doesnt give me the value as I expected

sushil353
Master II
Master II

Have you tried creating separate expression for two expressions.. ?

if possible attach a sample application

Anonymous
Not applicable
Author

what do you mean by creating separate expression?

Anil_Babu_Samineni

Try with this expression in Text object and then see whether value is coming or not

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)


From Above, What if you select 02-01-2016(DD-MM-YYYY). Which value you want to get, I mean Arbitrary

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
stabben23
Partner - Master
Partner - Master

Have you tried to separate them With parentheses?

(SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount))

+

(SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount))

Anonymous
Not applicable
Author

yes I did. but still didnt work

Anonymous
Not applicable
Author

when i run this in list box object:

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)

+

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)

it works perfectly. but when I try in pivot table it doesnt

SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)

my date period only contain the last day of every month e.g. 31-12-2015, 31-01-2016, 29-02-2016,....., 31-12-2016

if I select 31-01-2016 I hope to get amount of 31-12-2015