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

Expression for getting December month value for previous Year?

Hello,

I have a table as shown below.

1.PNG

My expression for selecting December month data of previous year for all the months for As Sold Upd OEC (Baseline) is not working.

=Aggr(NODISTINCT Sum({<Year = {$(vPreYear)} , Month = {$(vPreYearLastMonth)},  [Cost_Area] = {'Mechanical Engineering (labor + travel)'}, Snapshot_Date = >}Updated_OEC_Budget) , [Cost_Area])

What wrong am I doing here?

stalwar1‌ Can you help me on this?

Any help is appreciated? thanks

1 Solution

Accepted Solutions
sunny_talwar

Try this

Let vPreYear = '=Year(YearEnd(AddYears(max(Snapshot_Date),-1)))';

Let vPreYearLastMonth = '=Num(Month(YearEnd(AddYears(max(Snapshot_Date),-1))))';

and use this expression

Sum(TOTAL <Cost_Area> {<Year = {'$(vPreYear)'} , Num_Month = {'$(vPreYearLastMonth)'}, Cost_Code = {'3'}, Snapshot_Date = >}Updated_OEC_Budget)

View solution in original post

12 Replies
neelamsaroha157
Specialist II
Specialist II

Can you share a sample data?

bhaveshp90
Creator III
Creator III
Author

Please find the qvw and qvd for your reference.

thanks

neelamsaroha157
Specialist II
Specialist II

It looks like you did not define the variables vPreYear and vPreYearLastMonth in the 'variable overview'.

Also, there is no Updated_OEC_Budget for 'Mechanical Engineering (Labor+Travel)' cost area.

Check out the attached file.

bhaveshp90
Creator III
Creator III
Author

neelamsaroha1575‌, there is Updated_OEC_Budget for 'Mechanical Engineering (Labor+Travel)' as shown


1.PNG

neelamsaroha157
Specialist II
Specialist II

Looks like there was error in the spelling of Cost_Area value. now its working.

bhaveshp90
Creator III
Creator III
Author

You are right it is working. But when I try to create the same expression in my Original application it is still blank. I have added variables also in the Settings -> Variable Overview. Is there anything else you did? thanks

sunny_talwar

Are you making selections when you are viewing this? Selection in Snapshot_Date? May be try this

Only({<Year = {'$(vPreYear)'} , Month = {'$(vPreYearLastMonth)'}, Cost_Code = {'3'}, Snapshot_Date = >} Aggr(NODISTINCT Sum({<Year = {'$(vPreYear)'} , Month = {'$(vPreYearLastMonth)'}, Cost_Code = {'3'}, Snapshot_Date = >}Updated_OEC_Budget) , [Cost_Area]))

or this

Sum(TOTAL <Cost_Area> {<Year = {'$(vPreYear)'} , Month = {'$(vPreYearLastMonth)'}, Cost_Code = {'3'}, Snapshot_Date = >}Updated_OEC_Budget)

bhaveshp90
Creator III
Creator III
Author

thank you stalwar1‌ Yes, I am filtering using Snapshot_Date, I have used the expressions which are not giving the desired result.

Attached are the qvw, qvd for your reference.

thanks

Bhavesh

sunny_talwar

Your variables have no value..

Capture.PNG