topic Re: variance in QlikView

variance

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

hi i need a small help

iam showing the dashboard as month and years as current and previous year and now i need to add previous before year in this

2017 expression

=sum({<CalendarYear = {$(=year(today())-1)}>}Day_TotalNetAmt)

/sum({<Wrk_year = {$(=year(today())-1)}>}Wrk_day)

2018 expression

=sum({<CalendarYear = {$(=year(today()))}>}Day_TotalNetAmt)

/sum({<Wrk_year = {$(=year(today()))}>}Wrk_day)

need for 2016 expression ?

as variance i am showing the current year and previous year now i need to show as if we select 2017 and 2018 (or) 2017 and 2016 (or) 2018 and 2016 (or ) else need to show 2016,17,18 default variance to be displayed

variance expression:

=((sum({<CalendarYear = {$(=year(today()))}>}Day_TotalNetAmt)/sum({<Wrk_year = {$(=year(today()))}>}Wrk_day))

-(sum({<CalendarYear = {$(=year(today())-1)}>}Day_TotalNetAmt)/sum({<Wrk_year = {$(=year(today())-1)}>}Wrk_day)))

thanks in advance

Re: variance

mdmukramali — Tue, 09 Jan 2018 05:35:43 GMT

Hi,

If you select 2017 then do you need Variance between 2017 and 2016?

or do you want to consider 2018 also?

and can you sample some sample data file?

Re: variance

Anil_Babu_Samineni — Tue, 09 Jan 2018 05:48:26 GMT

Can you explain your Calendar Year returns, That means how many years it shows when filter as in?

You should make it as dynamic to work variance like

=((sum({<CalendarYear = {$(=Max(CalendarYear))}>}Day_TotalNetAmt)/sum({<Wrk_year = {$(=Max(CalendarYear))}>}Wrk_day))

-(sum({<CalendarYear = {$(=Max(CalendarYear)-1)}>}Day_TotalNetAmt)/sum({<Wrk_year = {$(=Max(CalendarYear)-1)}>}Wrk_day)))

Re: variance

eswarchandan — Tue, 09 Jan 2018 06:11:46 GMT

its show current date for the current year and previous date of previous year

Re: variance

Anil_Babu_Samineni — Tue, 09 Jan 2018 06:14:24 GMT

Can you make bit clear? 2015,2016,2017,2018 having the data. So, If you filter for 2016 and 2018 what you want to see the variance?

Re: variance

eswarchandan — Tue, 09 Jan 2018 06:16:43 GMT

if i select 2017 then variance between 2017 and 2018

if i select 2016 then variance between 2016 and 2017

sample data file i will upload soon

Re: variance

eswarchandan — Tue, 09 Jan 2018 11:06:31 GMT

I need to see the value of the variance when 2016 - 2018 = 2016 values as variance

Re: variance

sasikanth — Tue, 09 Jan 2018 11:21:21 GMT

Hi,

Try below one

use 2 expressions for Variance and enable based on condition

Variance:

Exp1 (Enable Condition : Getselectedcount(CalYear)>1 )

=((sum({<CalendarYear = {$(=min(CalYear))}>}Day_TotalNetAmt)/sum({<Wrk_year = {$(=min(CalYear))}>}Wrk_day))

-(sum({<CalendarYear = {$(=max(CalYear))}>}Day_TotalNetAmt)/sum({<Wrk_year = {$(=max(CalYear))}>}Wrk_day))

Exp2 (Enable Condition : Getselectedcount(CalYear)<=1 )

=((sum({<CalendarYear = {$(=max(CalYear))}>}Day_TotalNetAmt)/sum({<Wrk_year = {$(=max(CalYear))}>}Wrk_day))

-(sum({<CalendarYear = {$(=max(CalYear)-1)}>}Day_TotalNetAmt)/sum({<Wrk_year = {$(=max(CalYear)-1)}>}Wrk_day))

Re: variance

eswarchandan — Tue, 09 Jan 2018 11:27:30 GMT

thanks sasi k

I will try this and let know if it work out.

Re: variance

Anil_Babu_Samineni — Tue, 09 Jan 2018 13:57:12 GMT

May be this?

=((sum({<CalendarYear = {$(=Min(CalendarYear))}>}Day_TotalNetAmt)/sum({<Wrk_year = {$(=Min(CalendarYear))}>}Wrk_day))

-(sum({<CalendarYear = {$(=Max(CalendarYear))}>}Day_TotalNetAmt)/sum({<Wrk_year = {$(=Max(CalendarYear))}>}Wrk_day)))