Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
Creator III

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

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

1 Solution

Accepted Solutions
Specialist II

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])

10 Replies
Specialist II

would you be able to provide a sample file?

Master II

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)

Creator III
Author

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

Creator III
Author

thanks

Specialist II

Here you go -

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

Creator III
Author

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

Specialist II

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

Creator III
Author

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

Specialist II

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])

Community Browser