topic Re: Where not equal to in Table chart in QlikView

Where not equal to in Table chart

psk180590 — Fri, 17 Feb 2017 12:44:07 GMT

Hello All,

I have a table with data as below

Product

Market

How do i select products where Product is NOT EQUAL to Market. My final result should be

Product

Re: Where not equal to in Table chart

surendraj — Fri, 17 Feb 2017 12:52:24 GMT

In script

where Not match(Product,'Market')

Re: Where not equal to in Table chart

surendraj — Fri, 17 Feb 2017 12:53:59 GMT

where Not wildmatch(Product,'*Market*');

Re: Where not equal to in Table chart

sunny_talwar — Fri, 17 Feb 2017 13:00:42 GMT

Or this

Where Product <> 'Market';

Re: Where not equal to in Table chart

psk180590 — Fri, 17 Feb 2017 13:22:50 GMT

Hello stalwar1‌ and surendraj‌

Product Revenue

AA 100

BB 100

CF 100

HG 100

Market 1000

IJ 180

KL 150

out of this data i would need to display a table chart as follows

Product MarketShare

AA 10%

BB 10%

CF 10%

HG 10%

IJ 18%

KL 15%

Re: Where not equal to in Table chart

sunny_talwar — Fri, 17 Feb 2017 13:25:12 GMT

May be like this:

Sum({<Product -= {'Market'}>}Revenue)/Sum(TOTAL {<Product = {'Market'}>}Revenue)

Re: Where not equal to in Table chart

psk180590 — Fri, 17 Feb 2017 13:29:07 GMT

stalwar1 Thanks for the quick suggestion.

But, in the Product column i would need to display only the Products without Market and this would be as a dimension.

Re: Where not equal to in Table chart

sunny_talwar — Fri, 17 Feb 2017 13:30:46 GMT

That is exactly what my expression is going to do.

Dimension

Product

Expression

Sum({<Product -= {'Market'}>}Revenue)/Sum(TOTAL {<Product = {'Market'}>}Revenue)

Re: Where not equal to in Table chart

psk180590 — Fri, 17 Feb 2017 15:53:59 GMT

I'm not sure but this formula doesn't work for me.

It calculates all the products and shows the total sum for each.

So, in this example it gives me 73%(730/1000) against each product.

Re: Where not equal to in Table chart

sunny_talwar — Fri, 17 Feb 2017 15:58:06 GMT

Don't know, but this is working for me

Re: Where not equal to in Table chart

surendraj — Fri, 17 Feb 2017 16:02:06 GMT

Did u try with...

Sum(Revenue) / Sum(TOTAL Revenue)..

Multiply with 100 which gives you percentage or Check the Show in percentage option in the number tab.

Re: Where not equal to in Table chart

Fri, 17 Feb 2017 16:04:46 GMT

Hi,

Sunny Answer is correct.

if u have the issue still,could u pls share ur app once with us .

Re: Where not equal to in Table chart

psk180590 — Mon, 20 Feb 2017 10:55:40 GMT

Hello

manojqlik surendraj stalwar1

Thanks for the suggestions and hope every one had a nice weekend!!

I will try to explain my whole problem here.

Sales Person , Market, Product i have as Dimensions in my Table

And the formulas are as follows:

Sales Current Year = Sum(Sales) where Year=Current Year

Sales Last Year : Sum(Sales) where Year=(Current Year)-1

Difference : 2017-2016

Difference in %: (2017-2016)/2016

MarketShare : ?

(Sum({<Produkt -= {'Market'}>}Sales)/

Sum({<Produkt = {'Market'}>}Sales))

MarketShare in % : ?

(The % for above formula)

The first 4 formulas work fine but the formulas for Market share don't produce any results. I also tried setting the Product Dimension as Product=if(Product<>'Market',Produkt)

Re: Where not equal to in Table chart

Anil_Babu_Samineni — Mon, 20 Feb 2017 11:24:18 GMT

May be fault the Field Name

Instead of Produkt used Product

(Sum({<Product -= {'Market'}>}Sales)/Sum({<Product = {'Market'}>}Sales))

Re: Where not equal to in Table chart

psk180590 — Mon, 20 Feb 2017 12:09:16 GMT

loveisfail

It is a Typo mistake here, but in my Qlikview script i have it as Product.

Re: Where not equal to in Table chart

Anil_Babu_Samineni — Mon, 20 Feb 2017 12:10:52 GMT

Are you trying this in QlikView Script. Script won't support Set Analysis

but in my Qlikview script i have it as Product.

Re: Where not equal to in Table chart

psk180590 — Mon, 20 Feb 2017 12:12:47 GMT

I mean in my Set Analysis only.

Sorry for not being clear

Re: Where not equal to in Table chart

Anil_Babu_Samineni — Mon, 20 Feb 2017 12:53:38 GMT

Have you look Sunny's Reply. Same thing he used why it is not working for you? And can you elaborate more with your Original data and expected result?

Re: Where not equal to in Table chart

psk180590 — Mon, 20 Feb 2017 13:21:24 GMT

Hello All,

Attaching my original data in an CSV file and also the expected result stated below.

Area Market Product Date Sales(1+2+3) Units(1+2+3)

In the data where the Product column is Empty it has to be considered as Whole Market results

Ex: for Market D4 we have Product column as'Empty ' or 'CA'. Here i would like to see CA/Empty for each Area, Market, Product and Date .

Re: Where not equal to in Table chart

psk180590 — Mon, 20 Feb 2017 14:48:23 GMT

Hello All,

Has any one been able to look at the data and find something?