Hi Team,
i have requirement in Pivot table having Month, Day as Column dimensions and Customer, Product, Region as Row dimensions and metric, in this metric expression like pick and match combination. the requirement is when selecting AVG button, metric value shows correct where as EOP button selection means metric value should show only the maximum date value. suppose maximum date is loaded is 20210620 and value should show on this max date value when user selecting EOP button. i am able to achieve this EOP value in front end with following expression:
num(Sum(Aggr(If(date= Max(TOTAL <$(vAggregateDate), $(vDim1)> date), value),
date, $(vAggregateDate), $(vDim1))),'0.0000%').
Note here: vAggregateDate nothing but Month, HY, QT, FY and vDim1 is nothing but dimensions which are loading from csv
but now it is effecting performance issue when user selecting filters, it is taking more than 1 minute. Now i need to do this at script level so that performance will improve on the same. calendar, fact table and dimensions table are coming from different csv source files. i have tried till the following but still shows actual values(i.e AVG values) when selecting EOP button.
my tables are:
Calendar:
load date, Month, Day from calendar.csv
left join(Calendar)
load Month, Day, Max(date) as date , '1' as Flag resident Calendar group by Month, Day;
Fact:
load value1, value2, value3 from fact.csv
Dimension:
load Dimensions from Dimension.csv
i have tried : sum({<Flag={1}>}value) but no luck, showing AVG values only.
@sunny_talwar , @Gysbert_Wassenaar
