Expression in Tables in Script

[rahulsingh12]

Hi All,

I have a requirement where the data model is built. From there I need to built and display few KPI's and its value in a straight table

KPI                    Value

MTD Sales     50

YTD Sales       100

Daily Sales     10

There will be more than 100 such KPI' that I need to add in the table. One way to do it is to load the KPI's name in the inline table and write if statements with formula for each KPI.

Is there a way where I can write the KPI value expression in some table and just call it against each KPI without using multiple if statements?

[Saravanan_Desingh]

Yes, you can build the expressions as a Field and use it with the fact tables. Please see a sample which can help you to understand.

CalRules:
LOAD * INLINE [
    Name, Rule1, Rule2
    Today, =Today()
    Yesterday, "=Today()-1"
    Last 7 Days, ">=Today()-7"
    Last 10 Days, ">=Today()-10"
    Last 14 Days, ">=Today()-14"
    Last 30 Days, ">=Today()-30"
    This Week, ">=WeekStart(Today())"
    Last Week, ">=WeekStart(Today(),-1)", "<=WeekEnd(Today(),-1)"
    Last Two Weeks, ">=WeekStart(Today(),-2)", "<=WeekEnd(Today(),-2)"    
    This Month, ">=MonthStart(Today())"
    Last Month, ">=MonthStart(Today(),-1)", "<=MonthEnd(Today(),-1)"
    Last Two Months, ">=MonthStart(Today(),-2)", "<=MonthEnd(Today(),-2)"    
    This Year, ">=YearStart(Today())"
    Last Year, ">=YearStart(Today(),-1)", "<=YearEnd(Today(),-1)"
];

tab1:
LOAD Date(Today()-IterNo()+1) As tranDate
AutoGenerate 1
While IterNo() <=135
;
Left Join(tab1)
LOAD *
Resident CalRules;

tab2:
LOAD *, Evaluate(Chr(39)&tranDate&Chr(39)&Rule1&If(Not IsNull(Rule2),' And '&Chr(39)&tranDate&Chr(39)&Rule2)) As Eval, 
	Chr(39)&tranDate&Chr(39)&Rule1&If(Not IsNull(Rule2),' And '&Chr(39)&tranDate&Chr(39)&Rule2) As Str
Resident tab1;

tabCal:
//NoConcatenate
Mapping 
LOAD tranDate, Name
Resident tab2
Where Eval;

Drop Table CalRules, tab1, tab2;
//Map DateA, DateB, DateC, DateD Using tabCal;

Data:
LOAD RowNo() As RowID, *;
LOAD * INLINE [
    DateA, DateB, DateC, DateD
    1/26/2020, 12/31/2019, 1/1/2020, 4/1/2020
    5/8/2020, 5/10/2020, 5/3/2020, 4/22/2020
];

DataX:
CrossTable(Dates, Value)
LOAD RowID, DateA, DateB, DateC, DateD
Resident Data;

DataOut:
LOAD *, ApplyMap('tabCal',Value) As Name
Resident DataX;

Drop Table DataX;