topic Re: Set Analysis with variable in QlikView

Set Analysis with variable

Wed, 25 Nov 2020 16:16:04 GMT

I am attempting to create a set that uses a variable vMaxYear, which is the maximum year. It continuously gives me an error and I can't seem to figure out why

Trying to get Maximum year:

=SUM({<Year([Year&Month]) = {$(=vMaxYear)}>}-Revenue.Sales)

Trying to get Previous year:

=SUM({<Year([Year&Month])= {$(=vMaxYear)-1}>}-Revenue.Sales)

Any help is appreciated. Thank you

Re: Set Analysis with variable

Kushal_Chawda — Sun, 01 May 2016 07:08:18 GMT

try this

Current

=SUM({<Year = {"$(=vMaxYear)"}>} Revenue)

=SUM({<Year = {"$(=vMaxYear)-1"}>} Revenue)

Re: Set Analysis with variable

marcus_sommer — Sun, 01 May 2016 07:12:04 GMT

The syntax isn't quite correct. You could only filter/compare a field against a dataset and not against an expression. Therefore try something like this:

=SUM({< Year = {"$(=max(Year))"} >} Revenue.Sales)

and this will be only work if these period-fields are numeric (you couldn't apply a calculation like max() or an comparison like <=> with string-values) and the dataset-format is abolutely identically with the field-format. This meant a match by month from April and 4 will fail within set analysis.

If you haven't these fields as numeric fields within your datamodel I suggest to create them and use them within these calculations.

- Marcus

Re: Set Analysis with variable

Anonymous — Sun, 01 May 2016 08:53:02 GMT

Try Also

=Sum({<YearOrderDateKey={"$(=vVariable-1)"}>} SalesAmount)

of course use your field at the Syntax