topic Re: Write a conditional expression in QlikView

Write a conditional expression

prma7799 — Wed, 17 Feb 2016 05:34:10 GMT

Hi All,

I have fields called customer_code and sales_group and I have Sum(Grosssale) and Sum(netSale).

So I want to write an expression that if sale_group = ‘Gen’ , ‘Inst’, ‘imp’ and customer_code which start with ‘NP*’

then I want Sum(Grosssale) otherwise I want Sum(netSale).

Thanks

Re: Write a conditional expression

tamilarasu — Wed, 17 Feb 2016 05:36:36 GMT

Hi,

Try expression as,

If(Match(sale_group, ‘Gen’,‘Inst’,‘imp’ ) and WildMatch(customer_code,‘NP*’), Sum(Grosssale), Sum(netSale))

Re: Write a conditional expression

Anonymous — Wed, 17 Feb 2016 05:51:23 GMT

may be this?

=if(Sales_Group='Gen' or Sales_Group='Inst' or Sales_Group='Imp' and Customer_Code like 'NP*',

Sum(GrossSales), Sum(NetSale))

Re: Write a conditional expression

prma7799 — Wed, 17 Feb 2016 06:10:48 GMT

Hi Tamil,

Thanks for response.

Your expression getting write output in straight or pivot table when one or more dimension with customer_code but when I removed customer_code from dimension then the output is not giving Sum(Grosssale) its giving Sum(NetSale)

Thanks

Re: Write a conditional expression

MayilVahanan — Wed, 17 Feb 2016 06:18:04 GMT

Try like this

In Script,

If(Match(sale_group, ‘Gen’,‘Inst’,‘imp’ ) and WildMatch(customer_code,‘NP*’), GrossSales, NetSale) AS Sales

Then in your expression, simply use Sum(Sales)

Re: Write a conditional expression

sasikanth — Wed, 17 Feb 2016 06:20:55 GMT

try this

Sum(if(match(sale_group, ‘Gen’,‘Inst’,‘imp’ ) and wildmatch(customer_code,''NP*'),Grosssale,netSale)

Re: Write a conditional expression

tamilarasu — Wed, 17 Feb 2016 06:26:49 GMT

In that case,

If(Sum({<sale_group={'Gen','Inst','imp'},customer_code={'NP*'}>}Grosssale)=0,

Sum(netSale),

Sum({<sale_group={'Gen','Inst','imp'},customer_code={'NP*'}>}Grosssale))

Re: Write a conditional expression

jagan — Wed, 17 Feb 2016 06:31:54 GMT

Hi,

Try like this

If(MixMatch(sale_group, ‘Gen’ , ‘Inst’, ‘imp’) AND customer_code LIKE ‘NP*’, Sum(Grosssale), Sum(netSale))

Hope this helps you.

Regards,

Jagan.

Re: Write a conditional expression

Anonymous — Wed, 17 Feb 2016 06:31:58 GMT

sum({<Sales Group='Gen'>} Gross sales)

write the expression in this way want you want the values required in column

Re: Write a conditional expression

Anonymous — Wed, 17 Feb 2016 06:38:01 GMT

Did you try what I suggested you?

You can replace And operator with OR for same

Re: Write a conditional expression

prma7799 — Wed, 17 Feb 2016 06:55:45 GMT

Hi All,

I tried all expression which is given by you but not getting exact output.

Thanks

Re: Write a conditional expression

sasikanth — Wed, 17 Feb 2016 07:01:56 GMT

Please share your sample data if you can

Re: Write a conditional expression

tamilarasu — Wed, 17 Feb 2016 07:09:43 GMT

Yes. Please share sample file with expected output.

Re: Write a conditional expression

PradeepReddy — Wed, 17 Feb 2016 07:33:18 GMT

try like this..

Script:

If(match(sale_group, ‘Gen’ , ‘Inst’, ‘imp’) and wildmatch(customer_code,‘NP*’),'G','N') as Flag

Chart:

Expression 1: Sum({<Flag={'G'}>}Grosssale)

Expression 2: Sum({<Flag={'N'}>}netSale)

Expression 3: Sum({<Flag={'G'}>}Grosssale)+Sum({<Flag={'N'}>}netSale)

(Compare the 3rd expression result with the other 2 expressions)...

If still not getting, share the application...

Re: Write a conditional expression

Anonymous — Wed, 17 Feb 2016 07:35:29 GMT

SUM({<Sale_Group={'Gen'}>}GrossSale)

Re: Write a conditional expression

kaushiknsolanki — Wed, 17 Feb 2016 07:49:06 GMT

Try this.

Sum(Aggr(If(Match(sale_group, ‘Gen’,‘Inst’,‘imp’ ) and WildMatch(customer_code,‘NP*’), Sum(Grosssale), Sum(netSale)),Customer_Code))

Regards,

Kaushik Solanki

Re: Write a conditional expression

sfatoux72 — Wed, 17 Feb 2016 07:54:22 GMT

Ok,

You wrote :

Your expression getting write output in straight or pivot table when one or more dimension with customer_code but when I removed customer_code from dimension then the output is not givingSum(Grosssale) its giving Sum(NetSale)

What need to happen if customer_code first 2 characters or sale_group is not unique in your filtered set of data?

If a part of your data correspond to your condition to use Sum(Grosssale) and other part to Sum(NetSale).