Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.
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])))
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)
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
Does anyone has any opinion or direction?
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.
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
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])))
Hello,
Thank you for your reply, i did but, even zeros are gone just '-'s 😞 It didnt work.
Regards,
Tommy,
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:
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.