Ignore data filtering

[#1]

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

        ,0

       )

  )

   ))

  , [Equipment Operaciones]

  )

  )

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

Chart dimension:

Month-Year BS

[sunny_talwar]

Use set analysis for doing that

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

[#3]

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....

[sunny_talwar]

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

[#5]

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?

[sunny_talwar]

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.

[#7]

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

[sunny_talwar]

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.

[#9]

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?

[sunny_talwar]

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