|
 |
1 - Working with dates
i - Show month number: num(month(now()))
ii - Chronological ordering (good for graphs axis): DATE(MakeDate(ReqYear,ReqMonth),'yyyy-MMM')
iii - To change the date format of a dimension in a chart (ie pivot table) then edit the dimension
(this changes it to a calculated field), then apply points iv and v
iv - Returns the default date format (in load script):
makedate(year(MyDate), num(month(MyDate)), day(MyDate))
[NB: Concatenating the Day & Month & Year fields will work without the 'makedate', but the field
will be a string]
v - Returns a custom date format:
date(makedate(year(MyDate), num(month(MyDate)), day(MyDate)),'YYYY^MMM^DD')
vi - Quick test to find if a value is a true date: if(Date(MyDate),'Date','Not Date')
ii - Chronological ordering (good for graphs axis): DATE(MakeDate(ReqYear,ReqMonth),'yyyy-MMM')
iii - To change the date format of a dimension in a chart (ie pivot table) then edit the dimension
(this changes it to a calculated field), then apply points iv and v
iv - Returns the default date format (in load script):
makedate(year(MyDate), num(month(MyDate)), day(MyDate))
[NB: Concatenating the Day & Month & Year fields will work without the 'makedate', but the field
will be a string]
v - Returns a custom date format:
date(makedate(year(MyDate), num(month(MyDate)), day(MyDate)),'YYYY^MMM^DD')
vi - Quick test to find if a value is a true date: if(Date(MyDate),'Date','Not Date')
2 - Sample/Testing data
i - Load only a small number of records: use "First 500" just before the LOAD command
ii - Hide sheets based on a button (for dev sheets):
1 - Set up a variable vShowDevSheets
2 - Make a button on the spash screen with:
text: =if (vShowDevSheets = 1, 'Hide', 'Show') & ' Development Tabs'
action: set the variable value: =if(vShowDevSheets = 1, 0, 1)
button layout > Show > Condition: lower(OSUser()) = 'domain\My.Name'
3 - On each Dev Sheet > General > Show Select > Condition: vShowDevSheets = 1
ii - Hide sheets based on a button (for dev sheets):
1 - Set up a variable vShowDevSheets
2 - Make a button on the spash screen with:
text: =if (vShowDevSheets = 1, 'Hide', 'Show') & ' Development Tabs'
action: set the variable value: =if(vShowDevSheets = 1, 0, 1)
button layout > Show > Condition: lower(OSUser()) = 'domain\My.Name'
3 - On each Dev Sheet > General > Show Select > Condition: vShowDevSheets = 1
3 - Working with pivot charts
i - If you don't use an expression your chart will be empty, so include a dummy expression with one space
for the label and defintion as '=1' then expand the properties of the dummy dimension and change the
'Text Colour defintion' to white: RGB(255,255,255)
for the label and defintion as '=1' then expand the properties of the dummy dimension and change the
'Text Colour defintion' to white: RGB(255,255,255)
4 - Loads & Reloads
i - Add a text box to show last reload date: ='Last updated: ' &ReloadTime( )
ii - How to use variables in SQL statement sent to source (ie load only last month, rolling 12 months etc)
ii - How to use variables in SQL statement sent to source (ie load only last month, rolling 12 months etc)
5 - Selections and filters
i - Add a button to filter records (i.e. "Only Today's Data") by creating a new field in the load script
to return either the text 'Only Today's Data' or null() for each record,
(e.g. If(MyDate = Today(),'Only Todays Data',null()) as 'Today?'.
Then create a new list box showing the 'Today?' field, hide the caption and you have a button.
to return either the text 'Only Today's Data' or null() for each record,
(e.g. If(MyDate = Today(),'Only Todays Data',null()) as 'Today?'.
Then create a new list box showing the 'Today?' field, hide the caption and you have a button.
