Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;