topic Re: Creating Date filters in QlikView

Creating Date filters

Anonymous — Tue, 30 May 2017 03:33:46 GMT

I have data associated with Date in the table as below.

Date	ID	Amount
31996	1	100
31996	2	200
61996	3	300
61996	4	400
61996	5	500

I want to give From Date & To Date Filter separately as below, So that If I select the below combination I should get all the data available(for this combination) from above table.

If I'm using master calendar, the dates are converting to MM/DD/YYYY format. But I want the date format(MMYYYY) to be displayed as it is in filters.

I don't want to use cartesian join to frame From and To Date filters considering the data size.

Is there any other alternative ?

Thanks in Advance.

Re: Creating Date filters

prma7799 — Tue, 30 May 2017 03:43:17 GMT

Please share some sample data..

Re: Creating Date filters

Sergey_Shuklin — Tue, 30 May 2017 03:47:34 GMT

Hello, Sri B!

I think this should help: what is alternate states in Qlikview ?

Re: Creating Date filters

Anonymous — Tue, 30 May 2017 03:55:58 GMT

This is the sample data set. If I want to get the data associated with filter selections(only for 031995, 031996) what would be the approach ?

If I want to get the range (between 031995 to 031996) what is the approach ?

Date	ID	Amount
031995	6	10
031995	6	20
091995	10	10
121995	11	20
031996	1	100
031996	2	200
061996	3	300
061996	4	400
061996	5	500
091996	1	200
091996	2	300
121996	7	200
121996	7	300
092015	10	100
122015	10	100

LOAD Date(Date#(Date,'MMYYYY'),'MMYYYY') as Date, ID,Amount Inline [

Date ID Amount

031995 6 10

031995 6 20

091995 10 10

121995 11 20

031996 1 100

031996 2 200

061996 3 300

061996 4 400

061996 5 500

091996 1 200

091996 2 300

121996 7 200

121996 7 300

092015 10 100

122015 10 100

] (delimiter is spaces);

Re: Creating Date filters

Anonymous — Tue, 30 May 2017 04:14:12 GMT

Thanks Sergey. thats quick reply. I will check.

Re: Creating Date filters

antoniotiman — Tue, 30 May 2017 07:23:28 GMT

Hi,

see Attachment.

Regards,

Antonio

Re: Creating Date filters

Anonymous — Tue, 30 May 2017 11:02:42 GMT

Hi Antonio,

It is giving the selections on date range. Values are getting aggregated on 'ID' column. However I have to display all rows without aggregation on ID (ID is based on Date). For the same selection expected output should be:

Date	ID	Amount
031996	1	100
031996	2	200
061996	3	300
061996	4	400
061996	5	500
091996	1	200
091996	2	300
121996	7	500

Also If I want specific rows alone (without range) for Date selections, how I can frame date filter. Output on above selections should be

Date	ID	Amount
031996	1	100
031996	2	200
121996	7	200
121996	7	300

Thanks.

Re: Creating Date filters

antoniotiman — Tue, 30 May 2017 11:34:29 GMT

See Attachment

Re: Creating Date filters

Anonymous — Tue, 30 May 2017 13:23:44 GMT

One more question, I am already displaying set of data (Incurred till date) using the below condition using input box filters.

=num(sum({<Year={'>=$(=vStartYear)<=$(=vEndYear)'}>}[Amount]),'$#,##0')

How do I Incorporate above used Alternate states concept on existing condition-
sum({<Date={'>=$(=Min({Date1} Date))<=$(=Max({Date2} Date))'}>} Amount)

Here I have two filters, 1st is inputbox filters for ITD range.

2nd is Listbox filters with Alternate states (for filter selections within above filter range).

For Eg: I'm displaying ITD range Amount column using sum(Year= {>=(1990)<=(2000)}[Amount])

within this selection I want to show sum({<Date={'>=031996<=121996'}>}[Amount]).

Finally I should be able to see Amount values for this range >=031996<=121996.

is it possible in qlikview ? or any other approach ?

rwunderlich‌digvijay‌atkinsow‌swuehl‌jagan‌marcus_sommer‌