Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhaveshp90
Creator III
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

1.PNG

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)


1.PNG

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

thanks

Bhavesh

1 Solution

Accepted Solutions
neelamsaroha157
Specialist II
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])

View solution in original post

10 Replies
neelamsaroha157
Specialist II
Specialist II

would you be able to provide a sample file?

MK9885
Master II
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)

bhaveshp90
Creator III
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;

1.PNG

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
Creator III
Creator III
Author

Please reference the below comment

thanks

neelamsaroha157
Specialist II
Specialist II

Here you go -

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

bhaveshp90
Creator III
Creator III
Author

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

neelamsaroha157
Specialist II
Specialist II

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

images.jpg

bhaveshp90
Creator III
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

neelamsaroha157
Specialist II
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])