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: 
Not applicable

Ignore data filtering

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



17 Replies
Not applicable
Author

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

sunny_talwar

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

Not applicable
Author

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

sunny_talwar

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

Not applicable
Author

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.

sunny_talwar

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

sunny_talwar

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

sup
Contributor
Contributor

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