This topic contains 1 reply, has 1 voice, and was last updated by 
Frank 1 year, 11 months ago.
-
Posts
-
When you get in on Monday and you look at your flash report for yesterday, do you really want to see Friday – your last full selling day? But tomorrow, Tuesday, you want your flash to be for yesterday, which is Monday. So on Monday you want to see information from 3 days ago, while on every other day of the week you want to see information from 1 day ago (yesterday).
Turns out there is a way to determine what day of the week today is – then you can base a calc off that to show data for 3 days ago (or maybe you want to see and accumulation of Fri, Sat and Sun) or to show yesterdays data.
The datepart function will allow you to determine the day, it returns – 1=Sun,2=Mon, 3=Tue..etc.
The datepart function looks like this – datepart(“w”,Now())To use it in a calc it would look something like:
____________________________________________________________________________
//Verify there have been sales this year
Iif([Measures].[Data1 (Actual Sales Amount Jan TY to Aug TY)]<>0,//Check to see if it’s Monday, if so show sales from last Friday which
//is 3 days ago
Iif(datepart(“w”,Now()) = 2,
[Measures].[Data10 (Actual Sales Amount Sep 12 2017 )],//Otherwise show sales from Yesterday
[Measures].[Data11 (Actual Sales Amount Sep 14 2017 )]),//if there were no sales this year set the value to null
null)
______________________________________________________________________________* The // lines are just comments.
* The current date in the above example is Sept 15
* You’ll want to have a verification check on something (the first Iif in the above calc) so that you don’t get a
row for every member even if there is no info for that measure.
* You’ll need two measures for each value – one that is from Yesterday and one that is for 3 Days Ago. This is a
bit tedious but not very hard in Stratum.Let the fun begin! Hope this helps….
-
It’s Monday and yesterday is Friday……
Another lost weekend……:)
You must be logged in to reply to this topic.
