Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pamaxeed
Partner - Creator III
Partner - Creator III

'FROM' - 'TO' - Set analysis

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

11 Replies
senpradip007
Specialist III
Specialist III

Try somthing like:

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

tresesco
MVP
MVP

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

pamaxeed
Partner - Creator III
Partner - Creator III
Author

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.

lironbaram
Partner - Master III
Partner - Master III

hi

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

senpradip007
Specialist III
Specialist III

Can you post your sample qvw?

pamaxeed
Partner - Creator III
Partner - Creator III
Author

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

lironbaram
Partner - Master III
Partner - Master III

hi

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

pamaxeed
Partner - Creator III
Partner - Creator III
Author

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!

MarcoARaymundo
Creator III
Creator III

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