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.
