Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tommyl
Creator
Creator

Date filter is not working with large amount of data

Hello experts,

I have a problem and cannot understand the reason. 

In the load script my defined date variables are:

LET vServiceStart = Date( MonthStart( today() ) , 'YYYY-MM-DD');
LET vServiceEnd = Date( today() , 'YYYY-MM-DD' );

And the fields in the table, that i compare with are:

......

createdat,
timestamp(createdat,'YYYY-MM-DD hh:mm:ss[.fff] TT') as gmtcreatedat,
date#(trim(left(createdat,10)),'YYYY-MM-DD') as gmtcreateddate,
ConvertToLocalTime(createdat,'Brisbane') as aestcreatedat,
date#(trim(left(ConvertToLocalTime(createdat,'Brisbane'),10))) as aestcreateddate,

.......

 

I loaded the date like above. I created a pivot table with dimensions:

  • service_pstn.partner
  • billing_pstn.file
  • billing_pstn.class

And the measure is:

if(vTimeZone='AEST',sum(aggr(Sum{$<([service_main.aestcreateddate]={"<=$(vServiceEnd)>=$(vServiceStart)"}>}fabs([billing_pstn.points])),[service_pstn.partner],[billing_pstn.file],[billing_pstn.class])),sum(aggr(Sum({$<[service_main.gmtcreateddate]={"<=$(vServiceEnd)>=$(vServiceStart)"}>}fabs([billing_pstn.points])),[service_pstn.partner],[billing_pstn.file],[billing_pstn.class])))

The reason is to change the sum of points according to the selected timezone. (AEST or GMT) through "Variable Input" object. 

Whenever i check the data, this expression does not work with 23M record. But when i check it with 3 records, it works. 

Do you know the reason? I couldn't find a way with this approach? Could you please help me to fix this or could you please propose another way?  

 

Best Regards,

Tommy.

 

 

10 Replies
Vegar
Partner
Partner

You won't need to handle partner as I did, I just needed some values for my example. I assume you got partner as a dimension in your data? 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes