Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Contributor III

## YearToDate

Hi,

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

I have a simple table with :

 date Amount 2015/12/31 43315257 2020/06/30 10087819 2020/07/31 -1269887020 2020/08/30 7597395

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
MVP

Try this

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

19 Replies
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

Contributor III
Author

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/30 431494 2015/09/30 1664335 2015/10/30 1664335 2015/11/30 1664335 2015/12/31 1664335

and this is the result of my final expression :

 YEAR_MONTH amount 2015-08 431494 2015-09 1664335 2015-10 1664335 2015-11 1664335 2015-12 1664335
MVP

What is the output you are looking to get?

Contributor III
Author
 YEAR_MONTH amount 2015-08 431494 2015-09 1664335+431494 2015-10 1664335+1664335+431494 2016-11 1664335 2016-12 1664335+1664335

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

MVP

Try this

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

Contributor III
Author

I think it works !

my input:

 DATE Amount 2015/12/31 43315257 2020/06/30 9751498 2020/07/31 11145483 2020/08/30 7298001

the output:

 YEAR_MONTH Amount 2015-12 43315257 2020-06 9751498 2020-07 20896980 2020-08 28194982

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

input:

 END_DATE Amount 2015/08/30 431494 2015/09/30 1664335 2015/10/30 1664335 2015/11/30 1664335 2015/12/31 1664335

OUTPUT:

 YEAR_MONTH <= 2015-08 7088834 2015-09 6657340 2015-10 4993005 2015-11 3328670 2015-12 1664335

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 ?

MVP

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

Contributor III
Author

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

MVP

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

Community Browser