Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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)

View solution in original post

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)

View solution in original post

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