Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
bhaveshp90
Contributor 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

Tags (3)
1 Solution

Accepted Solutions

Re: Expression for getting December month value for previous Year?

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)

12 Replies
neelamsaroha157
Valued Contributor II

Re: Expression for getting December month value for previous Year?

Can you share a sample data?

bhaveshp90
Contributor III

Re: Expression for getting December month value for previous Year?

Please find the qvw and qvd for your reference.

thanks

neelamsaroha157
Valued Contributor II

Re: Expression for getting December month value for previous Year?

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
Contributor III

Re: Expression for getting December month value for previous Year?

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


1.PNG

neelamsaroha157
Valued Contributor II

Re: Expression for getting December month value for previous Year?

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

bhaveshp90
Contributor III

Re: Expression for getting December month value for previous Year?

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

Re: Expression for getting December month value for previous Year?

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
Contributor III

Re: Expression for getting December month value for previous Year?

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

Re: Expression for getting December month value for previous Year?

Your variables have no value..

Capture.PNG