topic Re: set analysis with variable value in QlikView

Current Year value for a particular variable value

Tue, 14 Oct 2014 07:21:39 GMT

Hi All,

I have requirement where I need to find out current year sales.Looks below expression is good for current year.

Sum({<Year = {'$(=Year(Today()))'}>}Sales)

But my requirement is i want to find out the current year sales based Variable value.

for example:

my Variable is "vIndicator",this variablewill store the values like A,B,C.

I want to find the current year sales where variable :vIndicator" value is 'A'

Sum({<Year = {'$(=Year(Today()))'}>,sum(if($(Indicator)='A')Sales)??

Any suggestions ?

Thanks

Re: set analysis with variable value

Tue, 14 Oct 2014 07:26:47 GMT

Hi Swathiraj,

How is your variable value?

If it is based on some field value, then you can use the below.

Sum({<Year = {'$(=Year(Today()))'}, Field={'$(=$(vIndicator))'}>})Sales)

Re: set analysis with variable value

martinpohl — Tue, 14 Oct 2014 07:27:13 GMT

Hello,

use

Sum({<Year = {'$(=Year(Today()))'}, Indicator ={'$(=vIndicator)'} >}Sales)

Regards

Re: set analysis with variable value

ashfaq_haseeb — Tue, 14 Oct 2014 07:27:23 GMT

Hi,

Try something like this

Sum({<Year = {'$(=Year(Today()))'},Field={'$(=vIndicator)'}>Sales)

Make sure you replace Field with your actual field

Regards

ASHFAQ

Re: set analysis with variable value

Tue, 14 Oct 2014 07:31:39 GMT

Thanks for your reply,

No my variable value is not based on field.

I need to sum the based on like this way

sum(if($(vIndicator)='A',Sales))--->it will give the all years sales where variable =A,

but I want only for current year.

Re: set analysis with variable value

Tue, 14 Oct 2014 07:39:06 GMT

Thanku Ashfaq but your requirement suits when we use field.

but my requirement looks like below

sum(if($(vIndicator)='A',Sales))--->it will give the all years sales where variable =A,

but I want only for current year.

Re: set analysis with variable value

ashfaq_haseeb — Tue, 14 Oct 2014 07:45:45 GMT

Post sample data with expected output.

why you need to filter with variable if it is not associated.

Regards

ASHFAQ

Re: set analysis with variable value

sasikanth — Tue, 14 Oct 2014 07:51:56 GMT

Tell me what is the expression/Logic your using in vIndicator

then it will be easy

Re: set analysis with variable value

Tue, 14 Oct 2014 13:30:05 GMT

Hi,

Because my variable has created multiple field condition from multiple tables.

here is the var condition,

If ([VAR ETO]='ETO', if([Suspension Flag - MM]<> '#' ,'A',

If(Today()- RDD = 0, 'B',

If([Qty Open] > 0 And RDD < Today(), 'C',

If([YearMonth RDD]=vYearMonthCurrentDate, 'D',

If( [YearMonth RDD]<>vYearMonthCurrentDate , 'E',

If([Qty Open] <=0, '4. Delivered'))))))

Now I want to find the sales only for variable value as A.

Re: set analysis with variable value

Wed, 15 Oct 2014 07:09:28 GMT

hi,

will it work to find vindicator = A for current year??

if(year=$(vCurrentYear),sum(if($(vIndicator)='A',Sales))

Re: set analysis with variable value

sasikanth — Wed, 15 Oct 2014 07:28:42 GMT

Try some thing like this

create a condion like this

vVar=if($(vIndicator)='A',max(Year)/Year(today())

sum({Year={'$(vVar)'}>}sales)

or else

use all the conditions like below

sum({<[Suspension Flag - MM]={'*'}-{'#'}, Year={$(=vCurrentYear)}>}sales

in the above expression use all the conditons those are equal to 'A'

Re: set analysis with variable value

Wed, 15 Oct 2014 08:12:02 GMT

Sasi, Looks your solution not working .

finally I got it here below the expression.

sum(if(Year=$(vCurrentYear),if($(vIndicator)='3. Due Later',[Undelivered Net value USD])))

Thanks to all for your valuable suggestions.