topic Re: YearToDate in QlikView

YearToDate

qlikviewuser20 — Wed, 30 Aug 2017 14:13:43 GMT

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 !

Re: YearToDate

effinty2112 — Wed, 30 Aug 2017 14:18:50 GMT

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

Re: YearToDate

qlikviewuser20 — Wed, 30 Aug 2017 14:40:53 GMT

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

Re: YearToDate

sunny_talwar — Wed, 30 Aug 2017 14:46:33 GMT

What is the output you are looking to get?

Re: YearToDate

qlikviewuser20 — Wed, 30 Aug 2017 14:52:50 GMT

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

Re: YearToDate

sunny_talwar — Wed, 30 Aug 2017 14:56:11 GMT

Try this

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

Re: YearToDate

qlikviewuser20 — Wed, 30 Aug 2017 15:08:26 GMT

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 ?

Re: YearToDate

sunny_talwar — Wed, 30 Aug 2017 15:10:54 GMT

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

Re: YearToDate

qlikviewuser20 — Wed, 30 Aug 2017 15:14:44 GMT

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

Re: YearToDate

sunny_talwar — Wed, 30 Aug 2017 15:18:52 GMT

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

Re: YearToDate

qlikviewuser20 — Wed, 30 Aug 2017 15:21:11 GMT

Can you just explain me, please, the expression proposed?

Re: YearToDate

sunny_talwar — Wed, 30 Aug 2017 15:31:50 GMT

I am doing accumulation over Year_Month but since this needed to be reset every year, I had to use Year as a dimension.

So, this is doing a accumulation

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

and this is to make sure that it resets every year

Aggr( ... , YEAR, (YEAR_MONTH, (NUMERIC)))

Re: YearToDate

qlikviewuser20 — Wed, 30 Aug 2017 16:24:09 GMT

Thank you very much.

Last question : if i have to display other dimension on my table did i have to include those dimension in the aggr function ?

Re: YearToDate

sunny_talwar — Wed, 30 Aug 2017 16:25:38 GMT

In that case, I would suggest you to look at thisThe As-Of Table‌

AsOfTable will remove the need to use Aggr() and will work beautifully with multi-dimensions

Re: YearToDate

qlikviewuser20 — Thu, 31 Aug 2017 08:08:49 GMT

So there is no other solution ?

Because i should not modify the script or add other tables :s

Re: YearToDate

sunny_talwar — Thu, 31 Aug 2017 11:20:46 GMT

There might be, but difficult to say without looking at it and also knowing the expected output

Re: YearToDate

qlikviewuser20 — Thu, 31 Aug 2017 11:35:29 GMT

The idea is exactly the same calculate the amount between the current year month and january of the current year and agrgegate by multiple dimensions just like that :

1	2	3	4	5	6		Amount
DIM1	A	DIM_A	DIM_3.1	DIM_4.1	DIM_5.1	2016-12	145575
					DIM_5.2	2016-12	94005055
					DIM5.3	2016-06	304435
					DIM5.4	2016-07	104423048
	B	DIM_B	DIM_3.2	DIM_4.2	DIM_5.1	2016-12	18607915
					DIM_5.2	2016-12	1460447
					DIM5.3	2016-06	17291174
					DIM5.4	2016-07	53658
					DIM5.6	2016-07	917048
					DIM5.7	2016-07	2572000

Re: YearToDate

sunny_talwar — Thu, 31 Aug 2017 11:38:38 GMT

Expected output?

Re: YearToDate

qlikviewuser20 — Mon, 04 Sep 2017 11:48:48 GMT

that was the expected output. Actually i have to calculate the sum of amount per date and per many other dimension. So for the dimension A and the sub_dimensions i have to calculate the amount of the current month until january of the current year.

Re: YearToDate

qlikviewuser20 — Mon, 04 Sep 2017 15:49:17 GMT

i attached in excel file the output expected.