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.

 

 

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

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

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

Does anyone has any opinion or direction?

 

Vegar
Partner
Partner

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

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

View solution in original post

tommyl
Creator
Creator

Hello,

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

Regards,

Tommy,

Vegar
Partner
Partner

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

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.