topic Re: How do i use variable's Value in sum of previous year in QlikView

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

Sat, 07 Jul 2012 08:15:47 GMT

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.

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

somenathroy — Sat, 07 Jul 2012 11:39:30 GMT

Hi Antony,

Can ypu please attach a demo qvw application.

Regards,

Som

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

Mon, 09 Jul 2012 05:26:10 GMT

Hi Som,

Thanks for your reply.

I can not attach the application.

Without sum how do i calcuate the expression.

Regards,

Antony.

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

Mon, 09 Jul 2012 05:33:56 GMT

Can any one help me to sort out this issue.......

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

giakoum — Mon, 09 Jul 2012 05:37:39 GMT

Hi.

put max(year)-1 into a variable and let us know the result. is it what you expect it to be?

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

Mon, 09 Jul 2012 05:43:57 GMT

i tried but its not working

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

giakoum — Mon, 09 Jul 2012 05:56:47 GMT

Is max(year) working? You need to concentrate on that, this may be your problem. If year is not a number for QV, the results you get are wrong.

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

Mon, 09 Jul 2012 06:00:16 GMT

for qty im taking the same year field only...

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

= sum({$<year={$(=max(year)-1)}>}BSC) - its not working due to variable(BSC)

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

Mon, 09 Jul 2012 06:24:36 GMT

Hi,

Please By Pass the year vaue first and then write the expression.

Sum({$<year = ,year={$(=max(year)-1)}>}BSC)

i hope this will help.

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

Mon, 09 Jul 2012 06:29:10 GMT

Hi Pratibha,

its not working....

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

Mon, 09 Jul 2012 06:41:10 GMT

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?

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

Mon, 09 Jul 2012 07:00:39 GMT

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....

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

Tue, 10 Jul 2012 11:38:43 GMT

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)))))

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

Tue, 10 Jul 2012 11:42:31 GMT

Hi Sir,

The above condition is working perfectly.

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

Regards,

Antony.

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

Tue, 10 Jul 2012 11:54:39 GMT

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

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

Tue, 10 Jul 2012 11:58:55 GMT

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.

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

Tue, 10 Jul 2012 11:59:12 GMT

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.

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

Tue, 10 Jul 2012 11:59:43 GMT

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.

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

Tue, 10 Jul 2012 12:01:42 GMT

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)