topic Re: Set analysis issue in QlikView

Set analysis issue

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set analysis issue

rubenmarin — Tue, 03 May 2016 12:44:32 GMT

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'

Re: Set analysis issue

sunny_talwar — Tue, 03 May 2016 12:45:23 GMT

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.

Re: Set analysis issue

Tue, 03 May 2016 13:37:37 GMT

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

Re: Set analysis issue

Tue, 03 May 2016 13:40:08 GMT

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)

Re: Set analysis issue

Tue, 03 May 2016 13:43:03 GMT

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)

)