Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
would you be able to provide a sample file?
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)
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
Please reference the below comment
thanks
Here you go -
Aggr(NODISTINCT Sum({<Year = {$(vPreYear)} , Month = {$(vPreYearLastMonth)}, [Cost Area] = {'Mechanical Engineering'}>}Updated_Sales) , [Cost Area])
thank you Neelam. But if we select 2018 the expression is null which is incorrect.
It doesn't get on my side. Are you selecting the date or the year field?
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
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])