topic Re: How do you refer to a year in set analysis in QlikView

How do you refer to a year in set analysis

aetingu12 — Sat, 16 Nov 2024 03:41:27 GMT

Dear all,

I have 3 years of monthly data (31 January 2017 until 31 March 2019) and I would like to get the end of year amounts for the past three years.

1 Column for each year, so

2017 would be 31 December 2017

2018 would be 31 December 2018

2019 would be 31 March 2019

I have written the expression as such:

if(ValueList('2017','2018','2019') = '2017',
sum({1 <Date={"=$(=YearEnd(addmonths(max(Date),-24)))"} >}BAL),
if(ValueList('2017','2018','2019') = '2018',
sum({1 <Date={"=$(=YearEnd(addmonths(max(Date),-12)))"} >}BAL),
if(ValueList('2017','2018','2019') = '2019',
sum({1 <Date={"=$(=max(Date))"} >}BAL))))

the dimension is just the Valuelist. I need to make sure that the Data selections won't effect the output. So it will always look at the latest data and go 2 years back, 1 year back and the current period.

Any help is very much appreciated.

Re: How do you refer to a year in set analysis

dplr-rn — Thu, 02 May 2019 15:47:29 GMT

I see you have {1 in your set analysis. that should work for ignoring all filters.
if you want to be specific, Add something like below into your set analysis
Year=,Month= etc. (substitute appropriate filter names)

Re: How do you refer to a year in set analysis

aetingu12 — Thu, 02 May 2019 16:05:23 GMT

Thank you for coming back to me. I have attached an example to show what I am talking about. It seems like I am getting the same amount for each year.

Re: How do you refer to a year in set analysis

dplr-rn — Thu, 02 May 2019 16:18:43 GMT

Issue is primarily = after the "

sum({1 <Date={"$(=max(Date))"} >}BAL)

also i would create a year, month columns (ideally a master calendar).

See attached

Re: How do you refer to a year in set analysis

rubenmarin — Thu, 02 May 2019 19:48:14 GMT

Hi, can you try with?:
Pick(match(ValueList('2017','2018','2019'), '2017','2018','2019')
,sum({<DATE={"$(=YearEnd(addmonths(max(DATE),-24)))"} >}AMOUNT)
,sum({<DATE={"$(=YearEnd(addmonths(max(DATE),-12)))"} >}AMOUNT)
,sum({<DATE={"$(=date(max(DATE)))"} >}AMOUNT)
)

Re: How do you refer to a year in set analysis

aetingu12 — Thu, 02 May 2019 20:13:48 GMT

As a quick follow up. I want to make sure that a date selection in a list doesn't effect the calculations. I tried putting 1 after sum({1<.... but a selection of the date is changing my table using your expression with pick.
Thanks,

Re: How do you refer to a year in set analysis

rubenmarin — Fri, 03 May 2019 06:40:58 GMT

The {1} should be for the DATE used to create the filters:

Pick(match(ValueList('2017','2018','2019'), '2017','2018','2019')
,sum({<DATE={"$(=YearEnd(addmonths(max({1} DATE),-24)))"} >}AMOUNT)
,sum({<DATE={"$(=YearEnd(addmonths(max({1} DATE),-12)))"} >}AMOUNT)
,sum({<DATE={"$(=date(max({1} DATE)))"} >}AMOUNT)
)

Re: How do you refer to a year in set analysis

aetingu12 — Fri, 03 May 2019 06:56:34 GMT

Thank you very much!