Set analysis issue

[#1]

Hi, I am new to Qlikview

Trying to do a compare analysis between 2 years.

I am using the following formulas

sum({<Year={$(=max(Year(time_id)))}>} sales)            ---- CURRENT YEAR

sum({<Year={$(=max(Year(time_id)-1))}>} sales)        ----- PREVIOUS YEAR

time_id being the column containing the date (belong to the time dimension).

The problem is that both expressions always give the same result. They both show results for the current year

How shoud I fix it to allow the second expression to allow it to show the results of the previous year instead of the current year?

thanks

[rubenmarin]

Hi, try substracting 1 from the max:

sum({<Year={$(=max(Year(time_id))-1)}>} sales) //Moved the -1 one parenthesys

Also, removing the label you can check the final expression, maybe time_id is a string (not a date) and the function returns null().

Dates are internally a number with Day as a unit, this number represents a date:

- Date(40000)='06/07/2009'

- Date(40001)='07/07/2009'

- Date(40000.05)='06/07/2009 12:00'

[sunny_talwar]

Have you made sure that Year and sales are not coming from tables which are not connected to each other in any way? Check the data model to make sure that there is a link between them.

[#4]

Hello Rubin

thanks for replying

I've actually subtracted the 1 from the max (as suggested), but it still gives the same result.

Also, time_id is a date. Below the table structure and some sample data

CREATE TABLE public.sales

(

  prod_id integer NOT NULL,

  cust_id integer NOT NULL,

time_id date NOT NULL,

  channel_id integer NOT NULL,

  sales_rep_id integer NOT NULL,

  quantity_sold integer NOT NULL,

  amount_sold integer NOT NULL,

  quantity_planned_sales integer,

  amount_planned_sales integer,

  cost integer,

  CONSTRAINT sales_channel_fk FOREIGN KEY (channel_id)

      REFERENCES public.channels (channel_id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION,

  CONSTRAINT sales_customer_fk FOREIGN KEY (cust_id)

      REFERENCES public.customers (cust_id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION,

  CONSTRAINT sales_product_fk FOREIGN KEY (prod_id)

      REFERENCES public.products (prod_id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION,

CONSTRAINT sales_time_fk FOREIGN KEY (time_id)

      REFERENCES public.times (time_id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

1;254;"1998-01-02";3;1;25;8539;28;6831;6831

1;2262;"1998-01-02";3;1;25;8539;28;6831;6831

1;38054;"1998-01-02";3;1;25;8539;28;6831;6831

1;6430;"1998-01-02";3;1;25;8539;28;6831;6831

1;10268;"1998-01-02";3;1;25;8539;28;6831;6831

1;5085;"1998-01-02";3;1;25;8539;28;6831;6831

1;11453;"1998-01-02";3;1;25;8539;28;6831;6831

1;3429;"1998-01-02";3;1;25;8539;28;6831;6831

1;11052;"1998-01-02";3;1;25;8539;28;6831;6831

1;1441;"1998-01-02";3;1;25;8539;28;6831;6831

[#5]

and there was a small mistake on my original posting.

The correct expresion that I am using in my app is

sum({<Year={$(=max(Year(time_id)-1))}>} quantity_sold)

[#6]

Hello Sunny,

thanks for replying

Yes time_id and quantity_sold come from the same table (sales table).

time_id is actually a foreign key in the sales table. It comes from the times dimension table.

Below the table structures

CREATE TABLE public.sales

(

  prod_id integer NOT NULL,

  cust_id integer NOT NULL,

time_id date NOT NULL,

  channel_id integer NOT NULL,

  sales_rep_id integer NOT NULL,

  quantity_sold integer NOT NULL,

  amount_sold integer NOT NULL,

  quantity_planned_sales integer,

  amount_planned_sales integer,

  cost integer,

  CONSTRAINT sales_channel_fk FOREIGN KEY (channel_id)

      REFERENCES public.channels (channel_id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION,

  CONSTRAINT sales_customer_fk FOREIGN KEY (cust_id)

      REFERENCES public.customers (cust_id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION,

  CONSTRAINT sales_product_fk FOREIGN KEY (prod_id)

      REFERENCES public.products (prod_id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION,

  CONSTRAINT sales_time_fk FOREIGN KEY (time_id)

      REFERENCES public.times (time_id) MATCH SIMPLE

      ON UPDATE NO ACTION ON DELETE NO ACTION

)

CREATE TABLE public.times

(

  time_id date NOT NULL, -- primary key; day date, finest granularity, CORRECT ORDER

  day_name character varying(9) NOT NULL, -- Monday to Sunday, repeating

  day_number_in_week smallint, -- 1 to 7, repeating

  day_number_in_month smallint, -- 1 to 31, repeating

  calendar_week_number smallint, -- 1 to 53, repeating

  fiscal_week_number smallint, -- 1 to 53, repeating

  week_ending_day date, -- date of last day in week, CORRECT ORDER

  week_ending_day_id integer,

  calendar_month_number smallint, -- 1 to 12, repeating

  fiscal_month_number smallint, -- 1 to 12, repeating

  calendar_month_desc character varying(8) NOT NULL, -- e.g. 1998-01, CORRECT ORDER

  calendar_month_id integer,

  fiscal_month_desc character varying(8), -- e.g. 1998-01, CORRECT ORDER

  fiscal_month_id integer,

  days_in_cal_month integer, -- e.g. 28,31, repeating

  days_in_fis_month integer, -- e.g. 25,32, repeating

  end_of_cal_month date, -- last day of calendar month

  end_of_fis_month date, -- last day of fiscal month

  calendar_month_name character varying(9) NOT NULL, -- January to December, repeating

  fiscal_month_name character varying(9), -- January to December, repeating

  calendar_quarter_desc character(7) NOT NULL, -- e.g. 1998-Q1, CORRECT ORDER

  calendar_quarter_id integer NOT NULL,

  fiscal_quarter_desc character(7), -- e.g. 1999-Q3, CORRECT ORDER

  fiscal_quarter_id integer,

  days_in_cal_quarter integer, -- e.g. 88,90, repeating

  days_in_fis_quarter integer, -- e.g. 88,90, repeating

  end_of_cal_quarter date, -- last day of calendar quarter

  end_of_fis_quarter date, -- last day of fiscal quarter

  calendar_quarter_number smallint, -- 1 to 4, repeating

  fiscal_quarter_number smallint, -- 1 to 4, repeating

  calendar_year smallint NOT NULL, -- e.g. 1999, CORRECT ORDER

  calendar_year_id integer NOT NULL,

  fiscal_year smallint, -- e.g. 1999, CORRECT ORDER

  fiscal_year_id integer,

  days_in_cal_year integer, -- 365,366 repeating

  days_in_fis_year integer, -- e.g. 355,364, repeating

  end_of_cal_year date, -- last day of cal year

  end_of_fis_year date, -- last day of fiscal year

  CONSTRAINT times_pk PRIMARY KEY (time_id)

)