Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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.

 

 

1 Solution

Accepted Solutions
Highlighted
Specialist II
Specialist II

Re: Date filter is not working with large amount of data

Hi Tommmy,

 

Try this :

 

if(vTimeZone='AEST',sum({$<([service_main.aestcreateddate]={"<=$(vServiceEnd)>=$(vServiceStart)"}>}

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])))

View solution in original post

10 Replies
Highlighted
MVP
MVP

Re: Date filter is not working with large amount of data

I don't see any problem with your table expression, although you have not specified what vServiceStart/End look like and how they are created/defined.

You may need to quote the variables in the set expression so that they are read as dates and not as a subtraction sum:

[service_main.aestcreateddate]={"<='$(vServiceEnd)'>='$(vServiceStart)'"

(assuming they are dates in Y-m-d format)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Creator
Creator

Re: Date filter is not working with large amount of data

Hello,

Thank you for your reply, 

Dates are in 'YYYY-MM-DD' format. 

createdat comes from the source as string and i create new fields from this field by formatting as above.

i put the quotes around the variables as you stated. But it didn't change:( 

Regards,

Tommy

 

Highlighted
Creator
Creator

Re: Date filter is not working with large amount of data

Does anyone has any opinion or direction?

 

Highlighted
Partner
Partner

Re: Date filter is not working with large amount of data

I notice that your variables are static, meaning that the internal is not a affected by your in app selections.

You could flag the transactions in the script and use that flag in your set modifier. I assume this will improve your calculation on the large data set.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Creator
Creator

Re: Date filter is not working with large amount of data

Hello,

Do you mean to create variables with "SET"? Could you please write what do you mean by flagging transactions in the script? 

Regards,

Tommy

Highlighted
Specialist II
Specialist II

Re: Date filter is not working with large amount of data

Hi Tommmy,

 

Try this :

 

if(vTimeZone='AEST',sum({$<([service_main.aestcreateddate]={"<=$(vServiceEnd)>=$(vServiceStart)"}>}

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])))

View solution in original post

Highlighted
Creator
Creator

Re: Date filter is not working with large amount of data

Hello,

Thank you for your reply, i did but, even zeros are gone just '-'s 😞 It didnt work. 

Regards,

Tommy,

Highlighted
Partner
Partner

Re: Date filter is not working with large amount of data

Consider this script. 

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

LOAD 
*,
if(createdat>='$(vServiceStart)' AND createdat<='$(vServiceEnd)', 1, 0) as _inService_createdat,
if(aestcreatedat>='$(vServiceStart)' AND gmtcreatedat<='$(vServiceEnd)', 1, 0) as _inService_aestcreatedat,
if(gmtcreatedat>='$(vServiceStart)' AND gmtcreatedat<='$(vServiceEnd)', 1, 0) as _inService_gmtcreatedat
;

LOAD 
rand()*100 as billing_pstn.points,
pick(ceil(rand()*4), 'PartnerA', 'PartnerB', 'PartnerC', 'PartnerD') as [service_pstn.partner],
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;

LOAD 
	timestamp( DayName(makedate(2020,1),ceil(IterNo()/3*rand())) + rand() )as createdat
AutoGenerate 1
While 
	DayName(makedate(2020,1),ceil(IterNo()/3*rand())) < YearStart(today(),1);

 

And these expressions:

aestcreatedat:
Sum({$<[_inService_aestcreatedat]={"1"}>}fabs([billing_pstn.points]))

gmtcreatedat
Sum({$<[_inService_gmtcreatedat]={"1"}>}fabs([billing_pstn.points]))

createdat:
Sum({$<[_inService_createdat]={"1"}>}fabs([billing_pstn.points]))

It can give you output similar to this:

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Creator
Creator

Re: Date filter is not working with large amount of data

Hello,

I really appreciate your efforts. This is amazing. One question, my partner count is more than 1K. How should i handle this? 

 

Regards,

Tommy.