Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
qlikviewuser20
New Contributor

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

Re: YearToDate

Try this

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

19 Replies
effinty2112
Honored Contributor

Re: YearToDate

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
New Contributor

Re: YearToDate

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

Re: YearToDate

What is the output you are looking to get?

qlikviewuser20
New Contributor

Re: YearToDate

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

Re: YearToDate

Try this

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

qlikviewuser20
New Contributor

Re: YearToDate

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 ?

Re: YearToDate

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
New Contributor

Re: YearToDate

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

Re: YearToDate

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