Full month / Part month-to-date / year-to-date

[#1]

Hi all,

I have been using the Community for a couple of years and have always found it so helpful I've never needed to ask a question ... until now

I have a fairly typical data model - invoices, calendar, products and customers

I am using the following expression to ensure a like for like comparison with the prior year - ie if we are 15 days into this month, take the same month last year / total working days last year * current working day in the period

if(YYYYMM=vCurrentPeriod,

Sum({$<Period={"=$(=Max(Period))"},Year= {"$(MaxYear-1>} Revenue )
/
only({$<Year= {"$(MaxYear-1)"}>} WorkingDaysInPeriod ) *  only({$<Year= {"$(MaxYear)"}>} WorkingDaysInPeriod_Current )  ,
Sum({$<Period={"=$(=Max(Period))"},Year= {"$(MaxYear-1)"},Revenue))

When one period is selected, this works fine

Now, if more than one period is selected, eg if periods 1-8 were selected in the example below, how can we get to 697.5? I've tried a number of approaches but just can't get to it

Any help greatly appreciated!

Period	2016	2015	2015 like-for-like
1	100	90	90
2	100	90	90
3	100	90	90
4	100	90	90
5	100	90	90
6	100	90	90
7	100	90	90
8	75	90	67.5
TOTAL	775	720	697.5

[Gysbert_Wassenaar]

The set is calculated at the chart level, not the row level. That's why your set analysis expression works only when you select one period. You can use an AsOf table where you link each period with it's like-for-like period of the previous year. See this document for an explanation of the AsOf table concept: Calculating rolling n-period totals, averages or other aggregations

[othniel2014]

show Column 2015 Period 8 = from 1 ago 2015 to 31 ago 2015 ???

and

Should be ....

Column "2015 like-for-like" = Selected Period  (From 1 ago 2015 to 15 ago 2015 for example) ???

ok...

First

generate this field in the script

--->  Num(DateField) as NumDate

Second

use this in the expression of yout graph

Selected Current Dates

--->  NumDate={$(='">= ' & $(=num($(=min(Num(DateField))))) & ' <= ' & $(=floor(num(max(DateField)))) &'"')}

Previous Dates of the Selected Current Dates

---> NumDate={$(='">= ' & $(=num($(=min(Num(AddYears(DateField,-1)))))) & ' <= ' & $(=floor(num(max(AddYears(DateField,-1))))) &'"')}

example...

Sum({$<

     NumDate={$(='">= ' & $(=num($(=min(Num(DateField))))) & ' <= ' & $(=floor(num(max(DateField)))) &'"')},

     Period={"=$(=Max(Period))"},

     Year= {"$(MaxYear-1>}

Revenue)

Third

Play with "1" and see the differences in total result

Example...

Actually in the expression begin :  Sum({$<

change for : ---->                          Sum({1<

or Change for ---->                       Sum({<

Play with "1" I use Sum({1<   For this case