topic Re: Re: show all values except one in QlikView

show all values except one

Thu, 27 Feb 2014 02:07:22 GMT

Hi,

I am trying to show all possible categories of car on x axis whether they have data for a specific month or not. For this purpose, I clicked on show all values, uncheck suppress missing and uncheck suppress zero-values.....it works like a charm and I get what I want. I get all categories on x axis so if there is missing or zero data, the graph goes down for that month

Now I have a bit change in the requirement. I want to exclude all cars that are red so I added the condition

car_color -= {'red'}

I am still seeing all the red cars on x axis. I want to remove all the red cars from the axis but want all the rest to stay there whther they are null or zero values

Can someone please tell me how to do this

Arif

Re: show all values except one

jagan — Thu, 27 Feb 2014 02:14:36 GMT

Hi,

Uncheck Show All values option, uncheck suppress missing and uncheck suppress zero-values this works. Hope this helps you. If not can you attach some sample file and use this car_color -= {'red'}.

Regards,

Jagan.

Re: show all values except one

Thu, 27 Feb 2014 02:17:41 GMT

that will also make the other zero-values/missing values categories disappear. I want them to stay on axis even if they are missing values. That helps us to see the drop in the graph for those months. Just want the red ones to disappear...not all of the zero/missing ones

Arif

Re: show all values except one

jagan — Thu, 27 Feb 2014 02:19:47 GMT

HI,

Can you attach the sample file.

Regards,

Jagan.

Re: show all values except one

Thu, 27 Feb 2014 02:30:55 GMT

Hi Syed,

1 traditional way is to add a calculated dimesnsion like: {there could be leading or trailing spaces in car_color}

if(car_color<>'red',car)

if(trim(car_color)<>'red',car)

In set analysis the condition that you tried should work:

{<car_color-='red'>}

{<car_color-='red'>,car_color={"=len(trim(car_color))<>0"}} not sure about this..

Regards,

Robinson

Re: Re: show all values except one

Thu, 27 Feb 2014 02:33:50 GMT

I have made the example very simple. There is listbox that has date...you need to select a date as a filter....and there are three items on the x axis and revenue on the y axis.

On x axis, I have shown cosmetics, fruits and Veg. I have made it so that these three items will always show on x axis whether they have data for the selected date or not. If there is no data for the selected date, the graph wil go down for that item.

Now for the sake of my problem and solution, I have tried to exclude the item fruit by putting the expression

sum({<Cat-={'Fruit'}>}Reven)

but it does not work. Please make it so that Fruit does not appear on the x axis but the other two items always stay on x axis.

Arif

Re: Re: show all values except one

Thu, 27 Feb 2014 02:37:22 GMT

I have used both of the above methods..but do not work

1) Calculated dimension only shrink the axis to those categories that only have positive values...the zero-values/missing valued categories for the selected month disappear if they dont have data for that month. I want the categories to alway show up. Calculated dimension disables the option of show all values

2) Using the condition in set analysis only brings the value down to zero...but the category still show up on the axis

You can try them both on the sample that i have attached

Arif

Re: Re: Re: show all values except one

jagan — Thu, 27 Feb 2014 02:50:30 GMT

Hi,

PFA file for solution.

Regards,

Jagan.

Re: show all values except one

sunilkumarqv — Thu, 27 Feb 2014 06:16:39 GMT

Try like this in you exp

sum({<Cat={"*"}-{'Friut'}>}Reven)

Hope you get the answer

Re: show all values except one

er_mohit — Thu, 27 Feb 2014 06:36:33 GMT

Try this

car_color = {"*"}-{"red"}