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

YearToDate

Hi,

thank you for your post.

I did use the YTD function but it doesn't work for me.

I have a simple table with :

dateAmount
2015/12/3143315257
2020/06/3010087819
2020/07/31-1269887020
2020/08/307597395

i did use this expression :

sum({<YEAR={$(=($(vCurrentYear)))}, DATE={"<=$(=Max(DATE))"}> }Amount)

to calculate the amount of the current month till the amount of january of the current year but it doesn't work

I also tried this expression:

Sum({< DATE={">=$(=Num(YearStart(Max(DATE))))<=$(=(Max(DATE)))"}>} Amount)

But same thing it just calculate the amount of the current month and i don't know how to fix that :s


Any help ?

Thank you very much !

1 Solution

Accepted Solutions
sunny_talwar

Try this

Aggr(RangeSum(Above(Sum({< DATE, MONTH, YEAR_MONTH>} Amount), 0, RowNo())), YEAR, YEAR_MONTH)

View solution in original post

19 Replies
effinty2112
Master
Master

Hi Iobna,

Is the current month selected in your UI? If so modify your set analysis:

sum({<YEAR={$(=($(vCurrentYear)))}, DATE={"<=$(=Max(DATE))", MONTH=}> }Amount)

This will cause the expression to disregard user selections in the MONTH field.

Good luck

Andrew

qlikviewuser20
Contributor III
Contributor III
Author

Thank you for your answer.

The current month is not selected but i added the condition ti disregard user selections in the month field but it doesn't change.

Here is my first table with just a sum expression without any condition:

DATE Amount
2015/08/30431494
2015/09/301664335
2015/10/301664335
2015/11/301664335
2015/12/311664335

and this is the result of my final expression :

YEAR_MONTH amount
2015-08431494
2015-091664335
2015-101664335
2015-111664335
2015-121664335
sunny_talwar

What is the output you are looking to get?

qlikviewuser20
Contributor III
Contributor III
Author

YEAR_MONTHamount
2015-08431494
2015-091664335+431494
2015-101664335+1664335+431494
2016-111664335
2016-121664335+1664335

i want to calculate the amount of the current month until january of the current year

sunny_talwar

Try this

Aggr(RangeSum(Above(Sum({< DATE, MONTH, YEAR_MONTH>} Amount), 0, RowNo())), YEAR, YEAR_MONTH)

qlikviewuser20
Contributor III
Contributor III
Author

I think it works !

my input:

DATE Amount
2015/12/3143315257
2020/06/309751498
2020/07/3111145483
2020/08/307298001

the output:

YEAR_MONTHAmount
2015-1243315257
2020-069751498
2020-0720896980
2020-0828194982

But i tried with another output and it seems that this is not working :

input:

END_DATE Amount
2015/08/30431494
2015/09/301664335
2015/10/301664335
2015/11/301664335
2015/12/311664335

OUTPUT:

YEAR_MONTH <=
2015-087088834
2015-096657340
2015-104993005
2015-113328670
2015-121664335

Another question please.

If i have to calculate the average and not the sum of the current month until january of the current year, did i have just to change the function to avg ?

sunny_talwar

Do you have QV12 or above? If you do... you can try this

Aggr(RangeSum(Above(Sum({< DATE, MONTH, YEAR_MONTH>} Amount), 0, RowNo())), YEAR, (YEAR_MONTH, (NUMERIC)))

For the Avg, may be change to RangeAvg() from RangeSum()

Aggr(RangeAvg(Above(Sum({< DATE, MONTH, YEAR_MONTH>} Amount), 0, RowNo())), YEAR, (YEAR_MONTH, (NUMERIC)))

qlikviewuser20
Contributor III
Contributor III
Author

No i have the 11.20 version and this expression doesn't work :s

sunny_talwar

In that case you will need to fix the sort order for YEAR_MONTH field in the script