YearToDate

[qlikviewuser20]

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 :

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 !

[effinty2112]

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]

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

[sunny_talwar]

What is the output you are looking to get?

[qlikviewuser20]

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

[sunny_talwar · Accepted solution]

Try this

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

[qlikviewuser20]

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

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]

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