topic Re: Ignore data filtering in QlikView

Ignore data filtering

Tue, 04 Oct 2016 10:40:58 GMT

I have a line chart which has an expression defined. The dimension of this chart is based on months of the year. This expression calculates a value based on a date range. This data range is: [Max Date Selected - 365 , Max Date Selected]

The problem comes when user filter data according to a date. Imagine user selects 01/01/2016 to 01/06/2016. The chart expression would be calculated based on this date range selected but I need expression to take into account below date range: [01/06/2016 - 365 days(a year), 01/06/2016]. The problem is that I do not have the data from 01/06/2016 - 365 days(a year) to 01/01/2016 because data is filtered according to date range selected 01/01/2016 to 01/06/2016.

So, how can I tell qlikview to calculate the expression using data no filtered (all data)?

I need to display months from [01/06/2016 - 365 days(a year), 01/06/2016] showing data calculated for each month using the expression in the same date range [01/06/2016 - 365 days(a year), 01/06/2016].

Chart expression:

=Interval(Sum(

Aggr(

if([Order Type Operaciones] ='PM12' and not(IsNull([Equipment Operaciones])) and Len(Trim([Equipment Operaciones]))>0,

if(

Count({$<[Order Type Operaciones] = {'PM12'},[Actual start (date)]={'>=AddYears($(=MinDateBS),-1)<=$(=MaxDateBS)'}>} [Operation/Activity]) > 1

(

MaxDateBS - Min({$<[Order Type Operaciones] = {'PM12'},[Actual start (date)]={'>=AddYears($(=MinDateBS),-1)<=$(=MaxDateBS)'}>} [Actual start (date)])

) / Count({$<[Order Type Operaciones] = {'PM12'},[Actual start (date)]={'>=AddYears($(=MinDateBS),-1)<=$(=MaxDateBS)'}>} [Operation/Activity])

if (

Count({$<[Order Type Operaciones] = {'PM12'},[Actual start (date)]={'>=AddYears($(=MinDateBS),-1)<=$(=MaxDateBS)'}>} [Operation/Activity]) = 0

,MaxDateBS - AddYears(MinDateBS,-1)

, if (

Count({$<[Order Type Operaciones] = {'PM12'},[Actual start (date)]={'>=AddYears($(=MinDateBS),-1)<=$(=MaxDateBS)'}>}[Operation/Activity]) = 1

,(MaxDateBS - AddYears(MinDateBS,-1)) / 2

)

))

, [Equipment Operaciones]

)

/ Count({$<[Order Type Operaciones] = {'PM12'}, [Equipment Operaciones]={"=Len(Trim([Equipment Operaciones]))>0"}>} DISTINCT [Equipment Operaciones]),'d') * 24

Chart dimension:

Month-Year BS

Re: Ignore data filtering

sunny_talwar — Tue, 04 Oct 2016 10:44:28 GMT

Use set analysis for doing that

{<Date = {"$(='>=' & Date(AddYears(Max(Date), -1), 'MM/DD/YYYY') & '<=' & Date(Max(Date), 'MM/DD/YYYY'))"}>}

Re: Ignore data filtering

Tue, 04 Oct 2016 11:02:04 GMT

But I do not understand one thing: If user has previously filtered data by selecting start and end date:

Start Date: 01/01/2016

End Date: 01/06/2016

then data before 01/01/2016 is not available because Qlikview has filtered it according date range selected by user despite I use your formula, right? maybe I don't understand Qlikview behaviour at all....

Re: Ignore data filtering

sunny_talwar — Tue, 04 Oct 2016 11:15:55 GMT

May be we can both help each other better if are able to provide a sample. I am not 100% sure what you are doing and I think it might help me if I can take a look at how you have setup everything

Re: Ignore data filtering

Tue, 04 Oct 2016 14:07:39 GMT

It is difficult to show it in a sample...

Basically my doubt now is: using set analysis it ignores the filter set? for example if user selects date range [01/05/2016, 01/10/2016], and if I force to take into account date range [01/01/2016,01/10/2016] using set analysis, then Are values in date range [01/01/2016, 01/05/2016] being taken? I think so, right? Could you confirm this?

Second, how can I do chart to show values in the same range the expression is calculated? Since data is filtered, only data in range [01/05/2016, 01/10/2016] is shown in the chart. So how can I force chart dimension to take into account the date range I want and to show these values? Can I use set analysis in the chart dimension? If so how?

Re: Ignore data filtering

sunny_talwar — Tue, 04 Oct 2016 14:11:55 GMT

To answer you first question, if you have created the range using Max or Min Date, you should be fine. Set analysis will over-ride selections made by using what has been specified in the set modifier.

To answer you second question, although you can use calculated dimensions for this, I would still use set analysis to do this. Set analysis will give better performance compared to a calculated dimension.

Re: Ignore data filtering

Tue, 04 Oct 2016 14:24:29 GMT

Great! I have force expression to take into account the date range I want using set analysis as you said but now my problem is that chart is not displaying all the data. Chart dimension is only taken into account the data filtered so I am trying to use set analysis in the dimension but I have no idea on how to specify it for the dimension. How can I specify a set analysis for the chart dimension? Many thanks for all you support and patience

Re: Ignore data filtering

sunny_talwar — Tue, 04 Oct 2016 14:26:34 GMT

I think you need to specify the set analysis in the chart expression and the dimension will automatically be take care of. Make sure to ignore selection in other date related fields such as Year, Month, etc where you might make a selection.

Re: Ignore data filtering

Tue, 04 Oct 2016 14:53:41 GMT

I have specified the set analysis in the chart expression but dimension is not being automaticallly adjusted (it only displays values according selected date range). Something is going wrong. Chart Dimension is a date field in the format MM-yyyy (chart displays values for months) and I think it is not ignoring the selection... so I wonder if there is some way to explicitly specify/force to use a set analysis for dimension with the same date range as chart expression. Is it possible?

Re: Ignore data filtering

sunny_talwar — Tue, 04 Oct 2016 14:55:59 GMT

Can you just provide few details

1) Expression you are using

2) Exact name of your dimensions

3) Exam field names where you are making selections

Re: Ignore data filtering

Tue, 04 Oct 2016 15:07:45 GMT

I only use 1 expression and 1 dimension, see last update in my question above.

Re: Ignore data filtering

sunny_talwar — Tue, 04 Oct 2016 15:10:16 GMT

Can you provide the name. For instance

Dimension

XYZ_DATE_FIELD

Expression

Sum({<XYZ_DATE_FIELD = {"$(.....)"}>} SALES)

Selections are made in

XYZ_YEAR_FIELD and XYZ_MONTH_FIELD

You can make up names, but atleast give me something to work with

Re: Ignore data filtering

Wed, 05 Oct 2016 08:22:39 GMT

What do you mean by selections made? Maybe I don't understand you at all...

Re: Ignore data filtering

sunny_talwar — Wed, 05 Oct 2016 09:15:21 GMT

It just mean that do you have fields where you make selections. Like a year selection or a month selection? I don't know how else explain this

Re: Ignore data filtering

Wed, 05 Oct 2016 09:29:26 GMT

Well, in the chart expression, the only fields that make selections are:

MinDateBS and MaxDateBS

which does the following:

=Date(Min(Basic_Start_Date), 'DD/MM/YYYY')

=Date(Max(Basic_Start_Date), 'DD/MM/YYYY')

but in these epxression, I do not use set analysis to indicate to take into account the range [01/06/2016 - 365 days(a year), 01/06/2016]. It will take into account the range selected by user. Anyway I changed them to take into account the correct range (not selected one) and chart continues not showing all data. Chart displays only the selected range.

Re: Ignore data filtering

sunny_talwar — Wed, 05 Oct 2016 09:45:41 GMT

Tony -

I have no idea what you are trying to say, but I am going to create my own sample and post it here and hopefully that will resolve your issue

Just bear with me for 10-15 minutes and hopefully you will like what you will see.

Best,

Sunny

Re: Ignore data filtering

sunny_talwar — Wed, 05 Oct 2016 09:47:43 GMT

Oh I just realized that you did add dimension and expression in your main post. When did you add that information there?

Re: Ignore data filtering

sup — Tue, 15 Oct 2019 07:59:54 GMT

I think he is about accounting a dimensions in max\min in set modifier of set analysis.