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