Write a conditional expression

[prma7799]

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

[prma7799]

Hi All,

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

Thanks

[sasikanth]

Please share your sample data if you can

[tamilarasu]

Yes. Please share sample file with expected output.

[PradeepReddy]

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

[Anonymous]

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

[kaushiknsolanki]

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

[sfatoux72]

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