topic Re: 'FROM' - 'TO' - Set analysis in QlikView

'FROM' - 'TO' - Set analysis

pamaxeed — Wed, 11 Jun 2014 10:10:39 GMT

Hi,

i have just a question.

In my usecase scenario i have a calendar with a from date and a to date, which covers a range of dates (Min(startDate), Max(endDate) of my CUSTOMER fact table).

The calendar table is not connected to any other tables in my datamodel

In my application i build two calendar boxes with 'FROM' - 'TO' data selection.

My question is that i thought i wanna go to use the set analysis to filter my data based on 'FROM' - 'TO' data selection.

Something like that:

Sum({$<CUST_STARTDATE= {">=$(=vStartDate)}, CUST_ENDDATE= {"<=$(=vEndDate)} >} Value)

My problem is that i not have only charts, but also tableboxes and charts without any real expression like SUM, AVG etc.

How i can accomplish this task without using a function in my expression?

Thanks,

Patric

Re: 'FROM' - 'TO' - Set analysis

senpradip007 — Wed, 11 Jun 2014 10:24:09 GMT

Try somthing like:

Sum({$<Date={">=$(vStartDate) <=$(vEndDate)"} >} Value)

Re: 'FROM' - 'TO' - Set analysis

tresB — Wed, 11 Jun 2014 10:25:19 GMT

Probably you need to implement date range selection like shown here: Re: Selecting Arbitrary Date Ranges

Re: 'FROM' - 'TO' - Set analysis

pamaxeed — Wed, 11 Jun 2014 10:28:03 GMT

Hi Pradip,

in my fact table Customer i have 2 dates FROM, TO, so it is not ok for me your solution.

In addition i wanna show a chart (pivot, straight) using set analysis but without any function like SUM, AVG etc. to filter my data.

Re: 'FROM' - 'TO' - Set analysis

lironbaram — Wed, 11 Jun 2014 10:31:04 GMT

why you don't you connect the calendar to the model

as far as i know in tablebox you want be able to determine what data to display

as you don't have expressions

how your ERD looks like , i would have tried to find a way to connect the calendar to the data

Re: 'FROM' - 'TO' - Set analysis

senpradip007 — Wed, 11 Jun 2014 10:34:10 GMT

Can you post your sample qvw?

Re: 'FROM' - 'TO' - Set analysis

pamaxeed — Wed, 11 Jun 2014 11:28:13 GMT

U are right it is the only way so far.

If i want to apply range selection. If i would connect my calendar to the datamodel i would not be able to work with >= and <= and apply range selection.

Or someone knows a better solution?

Attached u can find my demo application with the different solutions.

Patric

Re: 'FROM' - 'TO' - Set analysis

lironbaram — Wed, 11 Jun 2014 12:29:19 GMT

attach is an example using interval match

what it crate basically is list of date between min From to max To

then using interval match it creates for each customer the dates he was active

so if you choose a range of dates only customers that started before the min date and finished after max date will appear

Re: 'FROM' - 'TO' - Set analysis

pamaxeed — Wed, 11 Jun 2014 12:43:14 GMT

Hi Liron,

thanks for this other solution.

So as i understand it, the intervalmatch function creates for each customer a list of dates where he was active.

Selecting then the dates in the datelist, only the customer who were active in this dates will be shown, this means simply that it makes a kind of SELECTED_DATE BETWEEN FROM AND TO.

The only thing i not like of this solution is that in the data model i have a synthetic key, but for the rest i think it is a good solution. THANK U!

Re: 'FROM' - 'TO' - Set analysis

MarcoARaymundo — Wed, 11 Jun 2014 12:47:10 GMT

Sum({$<From={'>=$(=Min(FromDate))'}, To={'<=$(=Max(ToDate))'}>} Value)

Re: 'FROM' - 'TO' - Set analysis

lironbaram — Wed, 11 Jun 2014 12:54:07 GMT

hi patric

i this synthetic keys have bad reputation , more than they should

in this case as you know why the synthetic key was created it ok

the problem is when you have a synthetic key and you didn't planned to have one

Re: 'FROM' - 'TO' - Set analysis

pamaxeed — Wed, 11 Jun 2014 12:57:02 GMT

Yes i agree, u are right the problem is when u do not know why, for this purpose it could be tolerable ...anyway thank u Liron for ur help, i appreciate it.

Cheers,

Patric