topic Re: Comparison with the previous year in QlikView

Comparison with the previous year

Sat, 26 Apr 2014 19:03:19 GMT

I want to compare in a simple table the amount for the year I have selected with the preceding year.

I am making a mistake in the definition of the expression for the preeding year because the table does not display any data.

This is what I´m doing:

FOR THE YEAR SELECTED: It works

LABEL:

$(=Max(MbrName_Anio))

DEFINITION:

({$ <[MbrName_Anio]= {$(=Max(MbrName_Anio))} > } [Importe real])

FOR THE PRECEDING YEAR: The Label works but the Expression don´t

LABEL:It works

$(=Max(MbrName_Anio)-1)

DEFINITION: It doesn´t work

({$ <[MbrName_Anio]= {$(=Max(MbrName_Anio)-1)} > } [Importe real])

What am I doing wrong?

Thanks in advance for your answers!!

Re: Comparison with the previous year

Clever_Anjos — Sat, 26 Apr 2014 19:08:43 GMT

Basically you´re doing right.

Is it possible to post a app?

Re: Comparison with the previous year

stabben23 — Sat, 26 Apr 2014 19:14:05 GMT

Hi,

move your -1 inside brackets, like: {$(=max(MbrName_Anio -1))}

Re: Comparison with the previous year

stabben23 — Sat, 26 Apr 2014 19:23:04 GMT

Maybe MbrName_Anio is non numeric?

Re: Comparison with the previous year

trey_bayne — Sun, 27 Apr 2014 03:30:23 GMT

The Label is not paying attention to your Selection Set. It's just using the dollar sign expansion. That's why it works.

In your definition, you are defining the Selection Set as the current set using $, meaning that it's possible the Selection Set ( List Boxes highlighted green) references the current MbrName_Anio. If that's the case, you are eliminating MbrName_Anio-1 by selecting MbrName_Anio.

You can get around this by using ({1 <[MbrName_Anio]= {$(=Max(MbrName_Anio))} > } [Importe real]) or using an additional modifier to tell the Set Analysis Set to ignore a selection you've made in another QlikView Object ( List Box maybe?).

Re: Comparison with the previous year

SunilChauhan — Sun, 27 Apr 2014 03:41:32 GMT

Hi,

ru using any set aggr function like sum,count avg ...beacause set doesn't work without them

sum ({$ <[MbrName_Anio]= {"$(=Max(MbrName_Anio)-1)"} > } [Importe real]) .

if you attached sample file then that would be great to help you

Re: Comparison with the previous year

PrashantSangle — Sun, 27 Apr 2014 06:17:49 GMT

Hi,

Try several alternatives.

1. Write $(=Max(MbrName_Anio)-1) in single quote ' '

({<[MbrName_Anio]= {'$(=Max(MbrName_Anio)-1)'}>} [Importe real])

store value of $(=Max(MbrName_Anio)-1) expression in variable

Like create variable varPreviousYear

and value should be =Max(MbrName_Anio)-1

Then use that variable in your set analysis

({<[MbrName_Anio]= {"=varPreviousYear"}>} [Importe real])

Regards,