topic Re: Expression for getting December month value for previous Year? in QlikView

Expression for getting December month value for previous Year?

bhaveshp90 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a table as shown below.

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

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

neelamsaroha157 — Tue, 30 Oct 2018 15:09:41 GMT

Can you share a sample data?

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

bhaveshp90 — Tue, 30 Oct 2018 15:48:48 GMT

Please find the qvw and qvd for your reference.

thanks

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

neelamsaroha157 — Tue, 30 Oct 2018 16:09:03 GMT

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.

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

bhaveshp90 — Tue, 30 Oct 2018 18:04:12 GMT

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

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

neelamsaroha157 — Tue, 30 Oct 2018 18:31:27 GMT

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

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

bhaveshp90 — Tue, 30 Oct 2018 18:45:59 GMT

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?

sunny_talwar — Tue, 30 Oct 2018 18:51:17 GMT

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)

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

bhaveshp90 — Tue, 30 Oct 2018 19:12:57 GMT

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?

sunny_talwar — Tue, 30 Oct 2018 19:17:57 GMT

Your variables have no value..

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

bhaveshp90 — Tue, 30 Oct 2018 19:19:50 GMT

I have declared the variables in the back script as

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

vPreYearLastMonth =Month(YearEnd(AddYears(max(Snapshot_Date),-1)))

Is this something wrong in setting the variables

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

sunny_talwar — Tue, 30 Oct 2018 19:23:01 GMT

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)

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

bhaveshp90 — Tue, 30 Oct 2018 19:29:19 GMT

Awesome it worked. Thank you Sunny . I understood what should I do now