topic Qlikview- Fast Change- Need to neglect negative values in QlikView

Qlikview- Fast Change- Need to neglect negative values

Mon, 16 Mar 2015 06:51:20 GMT

Hi Experts,

I have a pie chart with pivot table as its fast change type.

I need to display only the positive values both in the Pie chart as well as pivot table.

my data appears as folows:

Subscription Type	Sales Amount
one time	39000
subscription	-2563

So i need to display only the value of Subscription Type=One time in my chart neglecting the Subscription Type=subscription.

My value in % calculation exceeds more than 100% in both charts.

May be i can use RELATIVE to make it 100% in pie chart but how could i do that in pivot table?

Thanks in advance!

Re: Qlikview- Fast Change- Need to neglect negative values

Mon, 16 Mar 2015 07:50:16 GMT

Hi,

Please Find the Attachment

Thanks

Manju

Re: Qlikview- Fast Change- Need to neglect negative values

linusblomberg — Mon, 16 Mar 2015 07:59:04 GMT

Percentage of what? of all sales?

you use set analysis to calculate the one time subscriptions and then you put that in relation to all sales (or whatever you want it to relate to).

sum({$<[Subscription Type]={'one time'}>}[Sales Amount])

sum(total [Sales Amount])

Re: Qlikview- Fast Change- Need to neglect negative values

Mon, 16 Mar 2015 08:06:10 GMT

Alternate way if you have more fieldvalues for Subscription Type...

=sum({$<[Subscription Type]-={'subscription'}>}[Sales Amount])

Re: Qlikview- Fast Change- Need to neglect negative values

Mon, 16 Mar 2015 08:14:13 GMT

Hi,

Just do one while loading data itself ignore the negative values in script

Temp:
LOAD * INLINE [
Sub Type, sales_Amount
one time, 39000
subscription, -2563
];

Inline:
NoConcatenate
load
*
Resident Temp where sales_Amount>=0;

DROP Table Temp;

and finally your front end expression will be like this

=sum(sales_Amount)/sum(TOTAL sales_Amount) and make into percentage in number tab

Thanks

Manju

Re: Qlikview- Fast Change- Need to neglect negative values

umerikhlas — Mon, 16 Mar 2015 08:24:25 GMT

Hope this will help you in neglecting negative values for subscription types..

just add a calculated dimension instead of adding "Subscription Type" as:

=if([Sales Amount]>=0,[Subscription Type])

and check "Suppress when value is null" for the same dimension you have just added

Now check.. it works..

Thanks and regards,