Show sales between 2 dates

[jerry_ile]

I'm a newbie, very newbie so pls be delicate only 2 weeks now

I have sales data for various host channels and need to show sales for This year and the equivalent time period last year.

With some community searching I ended up setting up some variables

vTYear     =Max([Acc Year])

vLYear   =Max([Acc Year])-1  e.g. 2017

vCurrentWk   =FirstSortedValue(distinct [Acc Week],-[Start Wk]) e.g. Wk_26

vCurrentStWk   =Max([Start Wk])  e.g. 24/06/2018

Wanted to set up a variable to show the [Start Wk] date for last year by looking up the equivalent Wk_ number but couldn't figure out the expression

vLYCurrentStWk   e.g. 25/06/2017    I tried this

=date(FirstSortedValue(distinct [Start Wk], aggr({<[Acc Year] = {$(vLYear)}>}, {<[Acc Week] = {$(vCurrentWk)}>})))

but it didn't work

Then intended to put a date range into a Set Analysis but again could not figure out the expression

I gave up on Variables and tried this but again no joy

sum({$<[Start Wk]={">=$(=date(FirstSortedValue( distinct [Start Wk], [Acc Year]= 2017)))<=$(=max([Start wk])-365)"}>} [Net GBP incVat])

The table would be similar to this

Start Wk	Acc Year	Acc Week	Host	Net GBP incVat
05/02/2017	2017	Wk_06	Channel A	100
25/06/2017	2017	Wk_26	Channel B	80
28/04/2018	2018	Wk_05	Channel A	150
06/05/2018	2018	Wk_19	Channel B	70
24/06/2018	2018	Wk_26	Channel B	100

And the results would look similar to this with a line showing the LY YTD sales

Can anyone shed some light please

[MK9885]

Not sure but...

Use below in your Calendar script...

if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,

num(Week(TempDate)) as WeekNum,

WeekStart(TempDate) as WeekStart,

Instead of TempDate field replace it with your DateField

In front end add a filter to setanalysis

sum({$<LastYearFlag= {1},WeekStart>} [Net GBP incVat])

Maybe this should help?

Lastyear Flag = 1 should always give you previous year, in this case it will be 2017 and if there is data for the week then it would automatically show the data for start week

If you still have trouble try to upload a sample data?

Thanks.

[jerry_ile]

Thanks Shahbaz

The InYear(TempDate, today(),-1), 1, 0) as LastYearFlag flagged the entire year for some reason not the Year to Date

But I did manage to add the flag to the data source so sum({$<LastYearFlag= {1}>} [Net GBP incVat]) is now doing the job

thanks for the help and steering me in the right direction

[MK9885 · Accepted solution]

For YTD maybe you should try

sum({$< Month = {"<=$(=num(Month))"} >} [Net GBP incVat])

Here Month field would have values such as Jan,Feb,Mar... etc.

LastYearFlag will always give you previous year which is static and will show 0 for 2016 or 2015...

to make it dynamic (select any year) you can use Year = { $(=Max(Year) - 1)}

sum({$< Month = {"<=$(=num(Month))"},Year = { $(=Max(Year) - 1)} >} [Net GBP incVat])

Now this will only get you previous years YTD not the current YTD

So use the 2nd expression but by default the value you see without year selection will be last years data and check if you need to add Month or Quarter as extra field for filter selections

Ex: sum({$< Month = {"<=$(=num(Month))"},Year = { $(=Max(Year) - 1)}, Month >} [Net GBP incVat])

If your questions has been resolved, please mark it correct and close it?

Thanks.