topic Re: conditional visible sum based on distinct currency in QlikView

conditional visible sum based on distinct currency

Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I have scenario

I have field called amount which is data like below

Rs 10000 IND

Rs 5000 IND

$ 35677 USD

$ 5286283 USd

€ 687898 EUR

€ 389080 EUR

so i want show total's when user select same(distinct) i.e Rs or $ or €

Total Rs 15000 IND

Rs 10000 IND

Rs 5000 IND

Total $ 5321960 USD

$ 35677 USD

$ 5286283 USd

like that,my qs is how to get that whether end user selected distinct Currency?

Please do the needful

Thanks

Thiru

Re: conditional visible sum based on distinct currency

sunny_talwar — Sat, 07 May 2016 01:56:05 GMT

May be this

Script

Table:

LOAD *,

SubField(Amount, ' ', 1) as Symbol,

SubField(Amount, ' ', 2) as Amount_Number,

SubField(Amount, ' ', 3) as Currency;

LOAD * Inline [

Amount

Rs 10000 IND

Rs 5000 IND

$ 35677 USD

$ 5286283 USD

€ 687898 EUR

€ 389080 EUR

];

Straight table chart

Dimension

Amount - > Hidden from the presentation tab

Expression

Symbol & ' ' & Num(Sum(Amount_Number), '#,##0') & ' ' & Currency

UPDATE: I have only proposed based on what I understood. You actual data may not look like this and I suggest you to share a sample which is closer to you actual scenario so that we can help you in a better way.

Best,

Sunny

Re: conditional visible sum based on distinct currency

Kushal_Chawda — Sat, 07 May 2016 03:12:25 GMT

Another way without hiding of anything

Currency:

Load * inline [

Currency

€

Rs ];

Use this currency in list box for selection

Data:

Load Amount,

Subfield(Amount,' ',-1) as Currency1,

Keepchar(Amount,'0123456789') as AmountNum

From table;

Create straight table

Add dimension you want to add

Exression:

Pick(match(Currency,'$','€','Rs'),

Currency&' ' &num(sum({<Amount={'$*'}>}AmountNum),'#,#00')&' '& Currency1,

Currency&' ' &num(sum({<Amount={'€*'}>}AmountNum),'#,#00')&' '& Currency1,

Currency&' ' &num(sum({<Amount={'Rs*'}>}AmountNum),'#,#00')&' '& Currency1)

Re: conditional visible sum based on distinct currency

Tue, 10 May 2016 09:16:55 GMT

Hi Sunny,

It is working fine

I have donecusing this below formula

if(count(DISTINCT [Sale Currency])=1,NUM(sum(saleamount),'#,##0')&' '&[Sale Currency],'-')

Thanks

Thiru