Set Analysis for selecting previous year's December data as base value for all the months?

[bhaveshp90]

Hello,

I want to select December month data of previous year as the baseline value for all the months for As Sold Dec (Updated OEC) as shown in the below image

But this expression is not working as shown in the image below (Snapshot_Date format like 2018-08-31, 2018-09-30, etc)

=Sum({<Month={"$(=max(Snapshot_Date)'}Snapshot_Date(MonthEnd(Max(Snapshot_Date), -1),'MMM-YY'))"}>}As_Sold)

What wrong am I doing here? Any help is greatly appreciated

thanks

Bhavesh

[neelamsaroha157]

would you be able to provide a sample file?

[MK9885]

Not sure if this would help , Maybe try

Sum({<Snapshot_Date = {"$(='>=' & Date(AddMonths(Max(Snapshot_Date), -1), 'YYYY-MM-DD')

& '<=' & Date(Max(Snapshot_Date), 'YYYY-MM-DD'))"}, Month, Num_Month, Year, Quarter, Week

>}As_Sold)

or shorter version can be

Sum({<Num_Month = { '$(=Max(Num_Month)  )'},Month, Year = {  $(=Max(Year)  )} >}As_Sold)

Num_Month field is numerics of months, 1, 2, 3...12.

I think above expression would give you Max of Month of selected Year..

If you just select Year=2017, it would shoe Dec-2017

If you want previous year Dec-16 when you select 2017

Sum({<Num_Month = { '$(=Max(Num_Month)  )'},Month,  Year = {  $(=Max(Year)  -1 )} >}As_Sold)

[bhaveshp90]

Please find the sample qvw in the attachments and the output (Baseline - Updated Sales) I am trying to get is shown below;

The Baseline Updated Sales for all the dates must be equal to the December month Updated Sales of the previous year.

(As shown above the Baseline - Updated Sales for all months in 2018 is equal to 2017 December month value = 1605652)

For 2018 December Baseline - Updated Sales value should be equal to 2018 December Updated Sales Value

thanks

[bhaveshp90]

Please reference the below comment

thanks

[neelamsaroha157]

Here you go -

Aggr(NODISTINCT Sum({<Year = {$(vPreYear)} , Month = {$(vPreYearLastMonth)}, [Cost Area] = {'Mechanical Engineering'}>}Updated_Sales) , [Cost Area])

[bhaveshp90]

thank you Neelam. But if we select 2018 the expression is null which is incorrect.

[neelamsaroha157]

It doesn't get on my side. Are you selecting the date or the year field?

[bhaveshp90]

I'm selecting the Date field. For April '2018-04-30' the expression is null also for 2018-02-28, 2018-01-31, 2018-03-31

[neelamsaroha157 · Accepted solution]

If you want to select on date, then modify your expression as  -

Aggr(NODISTINCT Sum({<Year = {$(vPreYear)} , Month = {$(vPreYearLastMonth)}, [Cost Area] = {'Mechanical Engineering'}, Date = >}Updated_Sales) , [Cost Area])