topic Working with calanders one date field with 2 seperate selections in QlikView

Working with calanders one date field with 2 seperate selections

vpanchuda — Sat, 16 Nov 2024 19:14:19 GMT

Can someone please guide me with the issue i am not bale to get the ogic of creating the below .

My data looks as below which has to be loaded,It has one date field.

The user is suppose to see select date range in 2 seperate filters with respect to ID.

USer need ID below 200 for a paticular date range and above 200 for paticular date rage,hence 2 date selections for one date field.

date1	ID	category	score
20-01-2020	100	INT	1
21-01-2020	110	exe	2
22-01-2020	300	dbe	3
22-01-2020	190	dbe	3
23-01-2020	200	dbe	4
24-01-2020	290	exe	5
25-01-2020	450	exe	6
25-01-2020	90	exe	6
24-01-2020	300	INT	7
25-01-2020	100	INT	8

I tried loading the same tabel twice and concatinating it as below

A:
LOAD date1,
date1 as Sel1,
category,
ID,
score
FROM
test.xlsx
(ooxml, embedded labels, table is Sheet1)

where ID<'200'
;

Concatenate

B:
LOAD date1,

date1 as Sel2,
category,
ID,
score
FROM
Desktop\test.xlsx
(ooxml, embedded labels, table is Sheet1)

where ID>'200'
;

in the front the user must slect the date range <200 so that the table shows the data less than 200 and user also must be able to select a seperate data range for >200 .

the date should be shown together for both ranges with dfferent where clauses in same table.

Can anyone help me with the approch or how to acheive it.

Thank you.

Re: Working with calanders one date field with 2 seperate selections

petter — Fri, 24 Jan 2020 11:19:01 GMT

"Alternate States" are created for exactly this type of scenario.

If you create an Alternate State called Sel1 and an additional Alternate State called Sel2 you can then set the listbox to the left to belong to Sel1 and the one to the right to belong to Sel2.

In an expression you can refer to selections in an alternate state by using the Alternate State name as a set identifier and also within the right hand side within a set modifer for a specific field by prefixing the field with the alternate state name using double colons as a separator: ::

Examples:

Sum( {Sel1} Amount ) and Sum( {Sel2 } Amount )

Sum( {<Date=Sel2::Date>} Amount )

For QlikView you can find more here:
https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Alternate%20States.htm

For Qlik Sense you can find more here:
https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/Visualizations/alternate-states-comparative-analysis.htm

Re: Working with calanders one date field with 2 seperate selections

vpanchuda — Fri, 24 Jan 2020 14:02:02 GMT

Hi Petter

thank yu for the esponse , i understand by creating alternate states i could split the dates and work with different selection but how to show both selections in one table is what i am stuck with.

Re: Working with calanders one date field with 2 seperate selections

petter — Fri, 24 Jan 2020 14:19:52 GMT

I understand and that is what I tried to explain. All the examples/expressions I showed above run in the default state and can access any alternate state and mix them in the same chart/table.

Re: Working with calanders one date field with 2 seperate selections

Brett_Bleess — Thu, 20 Feb 2020 17:58:30 GMT

Have a look at the following Design Blog post too, and I am posting the base URL to this area below that in case you wish to further search on your own.

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

If Petter's posts helped, do not forget to use the Accept as Solution button on the one that most helped, or if you have come up with a different solution that worked, consider posting that and then mark that as the solution.

Regards,
Brett

Re: Working with calanders one date field with 2 seperate selections

vpanchuda — Tue, 17 Mar 2020 16:13:58 GMT

Sorry being late, learned the concept from your post and worked well.

Thank you