topic Re: Comparing Values as an expression in Set Analysis in QlikView

Comparing Values as an expression in Set Analysis

Fri, 27 Jun 2014 09:53:42 GMT

Dear All,

I need to calculate total sales against regions (Japan, Asia, EURO),

where i need to have sales figure like this:

Sales for japan should be in JPY

Sales for Asia should be in USD

and Sales for EURO should be in EUR.

Now I have a table in which i calculated the currency as :

=MinString

(

If(GetCurrentField(drRegion)='REG_DESC',REG_CUR,

If(GetCurrentField(drRegion)='SUBREG',SUBREG_CUR,

If(GetCurrentField(drRegion)='COMP',COMP_CUR,

If(GetCurrentField(drRegion)='COMP2',COMP2_CUR,

If(GetCurrentField(drRegion)='ENT_SAP',ENT_SAP_CUR)))))

)

currency is coming correct when i take this expression alone against Regions as dimensions.

but when I calculate the sales figure like:

Sum(

{

ConvertedCurrency =

{

"$(=MinString

(

If(GetCurrentField(drRegion)='REG_DESC',REG_CUR,

If(GetCurrentField(drRegion)='SUBREG',SUBREG_CUR,

If(GetCurrentField(drRegion)='COMP',COMP_CUR,

If(GetCurrentField(drRegion)='COMP2',COMP2_CUR,

If(GetCurrentField(drRegion)='ENT_SAP',ENT_SAP_CUR)))))

))"

}

Sales)

This currency expression is not working correct means giving only 'EUR' against all the regions.

Re: Comparing Values as an expression in Set Analysis

Gysbert_Wassenaar — Fri, 27 Jun 2014 09:57:52 GMT

Set analysis calculates one set per chart, not a set per dimension value. If you're using region as a dimension you'll have to use the if statement.

Re: Comparing Values as an expression in Set Analysis

sasikanth — Fri, 27 Jun 2014 10:12:41 GMT

you can write 5 Exp , then you can enable them based on condition

not a good practice but one way to achieve it

sum({<currency={'REG_CUR'}>}sales)---> enabling condition if(getcurrentFiled(drRegion)='REG_DESC',1,0)

sililarly remaining

Re: Comparing Values as an expression in Set Analysis

Fri, 27 Jun 2014 11:15:17 GMT

Thanks Gysbert and Sasikanth for replying.

But REG_CUR is not a value, its again a field which contains different Regional Currencies for corresponding region.

if drill down 'drRegion' is 'REG_DESC' , then following REG_CURR should be used like:

JPY for japan,

EUR fro Euro,

USD for Asia