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 !

[qlikviewuser20]

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

[sunny_talwar]

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

[qlikviewuser20]

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 ?

[sunny_talwar]

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

[qlikviewuser20]

So there is no other solution ?

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

[sunny_talwar]

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

[qlikviewuser20]

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

[sunny_talwar]

Expected output?

[qlikviewuser20]

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.

[qlikviewuser20]

i attached in excel file the output expected.