topic Logic help in QlikView

Logic help

abhaysingh — Mon, 01 Feb 2016 05:01:03 GMT

Hi All,

Pls help me in this logic,

I have three values against product,

Say if i select month than it shud give me month value of product, else let say product found in a selected month, than it shud check overall year and give the value as per whole year, against null in selected month, else let against any product inMonth, and inYear value not found or than it will pick value from different table field let say Field1.

So what i have calculate yet is,

MOnth wise value, year wise value and Field1 value against product in a staright table,

Now i have a scenario of data like below

Product	Month Value	Year Value	Field1
A	10	12
B	11	13
C		10	20
D			10

Now u can see Against product A and B, there is a value against Month so need to pick first Month value, than u can see Against C, Month value is null, than need to pick Year value and for D , Month and Year value is null, than need to pick Field1 value.

For C, u can see there is a value for Year and Field1, first condition need to fulfill first, for C Year value, need to consider.

Re: Logic help

ajsjoshua — Mon, 01 Feb 2016 05:16:05 GMT

Hi,

Try with if statement

lik if monthvalue&Yearvalue is <=0,field1exp,Monthvalue and yearvalue exp.

Regards,

Joshua.

Re: Logic help

PradeepReddy — Mon, 01 Feb 2016 05:20:06 GMT

take a straight table..

Dimension : Product

Expression :

=if(len(trim([Month Value]))>0,[Month Value],

if(len(trim([Year Value]))>0,[Year Value],

if(len(trim(Field1))>0,Field1)))

Re: Logic help

Anonymous — Mon, 01 Feb 2016 06:39:20 GMT

like this?

Dim: Product

Expression: =sum(if( isnull([Month Value]), if( isnull([Year Value]), Field1,[Year Value]), [Month Value]))

Re: Logic help

abhaysingh — Tue, 02 Feb 2016 11:00:54 GMT

Hi,

I have exp like this

Month Value Rate

=((sum(total <ProdGrp> BudgetQty*BudgetRate *ExchangeRate)

sum(TOTAL <ProdGrp> BudgetQty* BudgetRate* ExchangeRate* PriceDifference)*.01)

Sum(TOTAL <ProdGrp>((BudgetQty*BudgetRate*ExchangeRate)+(BudgetQty*BudgetRate*ExchangeRate*(PriceDifference)/100))*(SpecDesc)/100))

sum(TOTAL<ProdGrp> BudgetQty)

Year Value Rate

=((sum(total <ProdGrp>{<Month>} BudgetQty*BudgetRate *ExchangeRate)

sum(TOTAL <ProdGrp>{<Month>} BudgetQty* BudgetRate* ExchangeRate* PriceDifference)*.01)

Sum(TOTAL <ProdGrp> {<Month>}((BudgetQty*BudgetRate*ExchangeRate)+(BudgetQty*BudgetRate*ExchangeRate*(PriceDifference)/100))*(SpecDesc)/100))

sum(TOTAL<ProdGrp> {<Month>} BudgetQty)

Sale Rate

=SaleRate

I want in above expression,

when Month Value Rate is blank or null give me Year Value Rate and if Year Value Rate is null or blank give me SaleRate

Kindly Help me in this

Re: Logic help

abhaysingh — Tue, 02 Feb 2016 11:27:07 GMT

any suggestion?

Re: Logic help

Anonymous — Tue, 02 Feb 2016 12:18:15 GMT

Try like this?

=if([Month Value Rate Expression]='' or null(),

if([Year Value Rate Expression]=' ' or null(), SalesRate, [Year Value Rate Expression]))

Re: Logic help

Anonymous — Tue, 02 Feb 2016 12:24:58 GMT

For your expression, try this?

Assuming your expression syntax is ok

=if((((sum(total <ProdGrp> BudgetQty*BudgetRate *ExchangeRate)

sum(TOTAL <ProdGrp> BudgetQty* BudgetRate* ExchangeRate* PriceDifference)*.01)

Sum(TOTAL <ProdGrp>((BudgetQty*BudgetRate*ExchangeRate)+(BudgetQty*BudgetRate*ExchangeRate*(PriceDifference)/100))*(SpecDesc)/100)) /sum(TOTAL<ProdGrp> BudgetQty)) = ' '

if((((sum(total <ProdGrp>{<Month>} BudgetQty*BudgetRate *ExchangeRate)

sum(TOTAL <ProdGrp>{<Month>} BudgetQty* BudgetRate* ExchangeRate* PriceDifference)*.01)

Sum(TOTAL <ProdGrp> {<Month>}((BudgetQty*BudgetRate*ExchangeRate)+(BudgetQty*BudgetRate*ExchangeRate*(PriceDifference)/100))*(SpecDesc)/100))

/sum(TOTAL<ProdGrp> {<Month>} BudgetQty)) = ' '

SalesRate

(((sum(total <ProdGrp>{<Month>} BudgetQty*BudgetRate *ExchangeRate)

sum(TOTAL <ProdGrp>{<Month>} BudgetQty* BudgetRate* ExchangeRate* PriceDifference)*.01)

Sum(TOTAL <ProdGrp> {<Month>}((BudgetQty*BudgetRate*ExchangeRate)+(BudgetQty*BudgetRate*ExchangeRate*(PriceDifference)/100))*(SpecDesc)/100))

/sum(TOTAL<ProdGrp> {<Month>} BudgetQty))))