How do i use variable's Value in sum of previous year

[#1]

Dear  All,

           How Do i use use variable's value for set analysis calculation in month & Year.

Variable is BSC

BSC =

sum((if(bil_bil_unit='2',(loa_rate*loa_act_wt)*1000,

//if(bil_bil_unit='3'or bil_bil_unit='2',((loa_rate*loa_act_wt)*1000),

if(bil_bil_unit='3' and loa_rate>10 and loa_rate<100,(loa_rate*loa_act_wt)*1000,

if(bil_bil_unit='3' or bil_bil_unit='5',(loa_rate*loa_act_wt),

if(bil_bil_unit='8' or bil_bil_unit='9' /*or bil_bil_unit='10'*/ or bil_bil_unit='13' or bil_bil_unit='21',(loa_rate*loa_dlv_qty),

//if(bil_bil_unit='10' or itemproduct='9940208',((loa_rate*loa_act_wt)*1000),

if(bil_bil_unit='27' and loa_dlv_qty>0,(loa_rate*loa_dlv_qty),

if(bil_bil_unit='27' or bil_bil_unit='34' and loa_cal_wt>0,(loa_rate*loa_cal_wt),

if(bil_bil_unit='27' or bil_bil_unit='34' and loa_act_wt>0,(loa_rate*loa_act_wt))))))))))

+

(sum((if(bil_bil_unit='10',(loa_rate*loa_dlv_qty)))))

Tha above formula working for max Year but not for previous year....

Eg :

Current Year = Sum({$<year={$(=max(year))}>}BSC) // its working

Prev Year = =Sum({$<year={$(=max(year)-1)}>}BSC) // giving wrong calculation, its showing me current year calculation.

Can any one help me.

Regards,

Antony.

[#2]

please try,

sum({<year =, Month=,Quarter=, year ={$(=max(max(year))-1)}>}BSC)

as this is working for me,Include your month and Quarters or week in the by pass if u have any.

Also please check the year format that the max(year) is returning,is it same with year.

I hope your year is in YYYY format ,right?

[#3]

Hi,

thanks for your reply....

its not working....

year in yyyy format only.... actually the problem is my above condition is working for Qty not BSC variable....

[#4 · Accepted solution]

try this

sum({$<year={$(=max(year))}>}

(if(bil_bil_unit='2',(loa_rate*loa_act_wt)*1000,

if(bil_bil_unit='3' and loa_rate>10 and loa_rate<100,(loa_rate*loa_act_wt)*1000,

if(bil_bil_unit='3' or bil_bil_unit='5',(loa_rate*loa_act_wt),

if(bil_bil_unit='8' or bil_bil_unit='9' or bil_bil_unit='13' or bil_bil_unit='21',(loa_rate*loa_dlv_qty),

if(bil_bil_unit='27' and loa_dlv_qty>0,(loa_rate*loa_dlv_qty),

if(bil_bil_unit='27' or bil_bil_unit='34' and loa_cal_wt>0,(loa_rate*loa_cal_wt),

if(bil_bil_unit='27' or bil_bil_unit='34' and loa_act_wt>0,(loa_rate*loa_act_wt))))))))))

+

(sum( {$<year={$(=max(year))}>} (if(bil_bil_unit='10',(loa_rate*loa_dlv_qty)))))

and for previous year

sum({$<year={$(=max(year)-1)}>}

(if(bil_bil_unit='2',(loa_rate*loa_act_wt)*1000,

if(bil_bil_unit='3' and loa_rate>10 and loa_rate<100,(loa_rate*loa_act_wt)*1000,

if(bil_bil_unit='3' or bil_bil_unit='5',(loa_rate*loa_act_wt),

if(bil_bil_unit='8' or bil_bil_unit='9' or bil_bil_unit='13' or bil_bil_unit='21',(loa_rate*loa_dlv_qty),

if(bil_bil_unit='27' and loa_dlv_qty>0,(loa_rate*loa_dlv_qty),

if(bil_bil_unit='27' or bil_bil_unit='34' and loa_cal_wt>0,(loa_rate*loa_cal_wt),

if(bil_bil_unit='27' or bil_bil_unit='34' and loa_act_wt>0,(loa_rate*loa_act_wt))))))))))

+

(sum( {$<year={$(=max(year)-1)}>} (if(bil_bil_unit='10',(loa_rate*loa_dlv_qty)))))

[#5]

Hi Sir,

The above condition is working perfectly.

the same calculation how do i do by using variable ????

Regards,

Antony.

[#6]

sum({<Year = {'$(vPrevYear)'}>}  Amount)

[#7]

The below mentioned code can be useful.

At first declare a variable by clicking Alt+Ctrl+V and name the variable as vPrevYear.

vPrevYear = Year(Max(Year)-1)

sum({<Year = {'$(vPrevYear)'}>}  Amount)

Thanks & Regards,

Koushik.

[#8]

The below mentioned code can be useful.

At first declare a variable by clicking Alt+Ctrl+V and name the variable as vPrevYear.

vPrevYear = Year(Max(Year)-1)

sum({<Year = {'$(vPrevYear)'}>}  Amount)

Thanks & Regards,

Koushik.

[#9]

The below mentioned code can be useful.

At first declare a variable by clicking Alt+Ctrl+V and name the variable as vPrevYear.

vPrevYear = Year(Max(Year)-1)

sum({<Year = {'$(vPrevYear)'}>}  Amount)

Thanks & Regards,

Koushik.

[#10]

The below mentioned code can be useful.

At first declare a variable by clicking Alt+Ctrl+V and name the variable as vPrevYear.

vPrevYear = Year(Max(Year)-1)

Thanks & Regards,

Koushik.

sum({<Year = {'$(vPrevYear)'}>}  Amount)