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

 

 

1 Solution

Accepted Solutions
preminqlik
Specialist II
Specialist II

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
jonathandienst
Partner - Champion III
Partner - Champion III

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
tommyl
Creator
Creator
Author

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

 

tommyl
Creator
Creator
Author

Does anyone has any opinion or direction?

 

Vegar
MVP
MVP

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.

tommyl
Creator
Creator
Author

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

preminqlik
Specialist II
Specialist II

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

tommyl
Creator
Creator
Author

Hello,

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

Regards,

Tommy,

Vegar
MVP
MVP

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

tommyl
Creator
Creator
Author

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.