topic fields comparison in QlikView

fields comparison

mambi — Mon, 14 Apr 2014 08:39:24 GMT

Hi everyone,

from the exemple below i want to know if the product price had changed over years and display the result ( changed or unchanged ) in the COMP column

the number of years is more important so i don't want to use many nested if

Pord	YEAR 1	YEAR 2	YEAR 3	YEAR 4	COMP
A	12	13	14	15
B	12	12	12	12
C	14	14	14	15

any idea ?

Re: fields comparison

MarcoWedel — Mon, 14 Apr 2014 08:54:22 GMT

Hi Mambi,

tabInput:
CrossTable (year, [product price], 1)
LOAD * FROM [http://community.qlik.com/thread/114194]
(html, codepage is 1252, embedded labels, table is @1);

tabChanged:
LOAD
  Pord,
  if(Count(DISTINCT [product price])>1, 'changed', 'unchanged') as COMP
Resident tabInput
Group By Pord;

hope this helps

regards

Marco

Re: fields comparison

Mon, 14 Apr 2014 09:02:45 GMT

Don't know how to avoid using if. Just try to use less if.

How about compare individual number with the average number? but you still need 4 if...it's not good enough I think.

Re: Re: fields comparison

Mon, 14 Apr 2014 09:05:02 GMT

Mambi,

you may also like something more dynamic, with year selection possibility and check change for selected years.

I used Marco sample and added some chart.

Regards

Darek

Re: fields comparison

ToniKautto — Mon, 14 Apr 2014 09:12:27 GMT

I agree with Marco's solution.

In this scenario the key is to make the data model handle the year as a field, so that you can apply selections and aggregation by year. From the structure of your incoming data the Crosstable() function will help rotate the data in to the preferred order.