topic Hide or remove month in QlikView

Hide or remove month

Anonymous — Fri, 10 Feb 2012 20:09:18 GMT

Hi guys,

I have a bar chart by Months (I calculate month----- MonthName(Monthstart(submitDateTime)) as MYear).

Some months have count less than 20. How can I remove or hide these months?

Thanks in advance,

Hide or remove month

Fri, 10 Feb 2012 20:47:50 GMT

Yo can do:

if(count(values) < 20, 0, count(values)

and check supress zero values in the presentation tab

Hide or remove month

Anonymous — Fri, 10 Feb 2012 20:56:06 GMT

Thanks Federico for your help.

It worked, but I'd like to remove or hide Aug 2011. if it possible?

Thank you again,

Hide or remove month

Anonymous — Fri, 10 Feb 2012 21:01:45 GMT

It is possible by repalcing dimension field in your chart with calculated dimension. Simple example (it may be more complex in your situation):

aggr(if(sum(Amount)>20, Month),Month)

You can make it more flexible replacing 20 with a variable, so you can use it in an input box, set default to 20, and be able to change to any number.

Hide or remove month

Anonymous — Fri, 10 Feb 2012 21:05:05 GMT

I found out issue:)

if(aggr( count([Overall Experience Scores]) , MYear) > 30 , aggr( Avg([Overall Experience Scores]) , MYear) , null())

Thanks everybody for your help,

Hide or remove month

Fri, 10 Feb 2012 21:06:31 GMT

Maybe you should add some fields in your script for a better managment of the calendar.

I use this one

LET varMinDate = Num('01/04/2010');

LET varMaxDate = num(today());

LET vToday = num(today());

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

$(varMinDate) + rowno() - 1 AS Num,

date($(varMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

$(varMaxDate) - $(varMinDate) + 1;

//*************** Master Calendar ***************

MasterCalendar:

LOAD TempDate AS submitDateTime,

Week(TempDate) AS Week,

Year(TempDate) AS Year,

Month(TempDate) AS Month,

Day(TempDate) AS Day,

Weekday(TempDate) AS WeekDay,

'Q' & ceil(month(TempDate) / 3) AS Quarter,

Date(monthstart(TempDate), 'MMM-YYYY') AS

MonthYear,

Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

inyeartodate(TempDate, $(vToday), 0) * -1 AS

CurYTDFlag,

inyeartodate(TempDate, $(vToday), -1) * -1 AS

LastYTDFlag

RESIDENT TempCalendar

ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

After you got the calendar, you could use month as dimension in your chart, and a set analysis with MonthYear to eliminate any monthyear you want.

Hide or remove month

Anonymous — Fri, 10 Feb 2012 21:33:54 GMT

What's wrong in this expression:

if(aggr( count(Membership) , MYear) > 60 , (aggr( Count(Membership) , MYear)/

if(aggr( count(Membership) , MYear) > 60 , aggr( Count(TOTAL Membership) , MYear))) , null())

It works:

Count(Membership)/

Count(TOTAL <MYear> Membership)

I want to apply condition to show months where more than 60 people.

Thanks,

Hide or remove month

Anonymous — Fri, 10 Feb 2012 21:40:23 GMT

Probably this:

if(Count(Membership)>60, Count(Membership)/Count(TOTAL <MYear> Membership), null())

Hide or remove month

Anonymous — Fri, 10 Feb 2012 21:50:14 GMT

Thanks Michael,

The second part works, but the first not.

My Membership has Member, Not Member and etc.

So I tryed

if(Count(TOTAL Membership)>60, Count(Membership)/Count(TOTAL <MYear> Membership), null())

It shows all months.

Hide or remove month

Fri, 10 Feb 2012 21:58:05 GMT

You are using TOTAL for each month, remove total from the validation.

Hide or remove month

Anonymous — Mon, 13 Feb 2012 15:47:31 GMT

I can't remove TOTAL, because Membership has tags. If I remove TOTAL, it'll use only tags which have more than 60 answers.

Thanks,

Hide or remove month

Mon, 13 Feb 2012 16:01:17 GMT

But if you use TOTAL for the validation, you will have the same result for all validations!

Hide or remove month

Anonymous — Mon, 13 Feb 2012 16:14:20 GMT

I use only these months where TOTAL count answers more than 60.

For example:

In Nov 2011:

Membership: gr1-15: gr-2- 35; gr-3-40;gr-4-70. TOTAL=150

In Dec 2011:

Membership: gr1-6: gr-2- 10; gr-3-10;gr-4-5. TOTAL=31

In Jan 2012:

Membership: gr1-25: gr-2- 45; gr-3-100;gr-4-70. TOTAL=240.

In chart I should show only Nov and Jan.

Thanks,

Hide or remove month

Mon, 13 Feb 2012 16:20:29 GMT

Can you uplaoad a sample qvw?

Re: Hide or remove month

Anonymous — Mon, 13 Feb 2012 16:42:05 GMT

Please find attached file.

I want to remove Months where TOTAL count less than 60.

Thanks,

Re: Hide or remove month

Mon, 13 Feb 2012 16:54:25 GMT

Like this?

Hide or remove month

Anonymous — Mon, 13 Feb 2012 17:01:16 GMT

This expression works, but where answers for each tags more than 60.

if(Count(Membership)>60, Count(Membership)/Count(TOTAL <MYear> Membership), null())

but where I have tags which have less than 60 answers (but TOTAL COUNT more than 60), the expression doesn't show them.

Other words, for example, in November Membership has tag1-70, tag2-150, tag3-200, tag4-45. (TOTAL=465)

Tag4 won't be show, because it's less than 60.

That's why I want to use TOTAL count. I need to show only these months where TOTAL count less than 60.

Thanks,

Hide or remove month

Mon, 13 Feb 2012 17:05:54 GMT

But that info is in the qvw?

I can only see the fields qID_5395_Aware of Bes Price Guarantee and Brand that are non-date related fields.

Hide or remove month

Anonymous — Mon, 13 Feb 2012 17:09:40 GMT

Thanks, It works!!!!

if(Count(TOTAL <MYear> [Membership])>60,Count([Membership])/

Count(TOTAL <MYear> [Membership]), null())

Thanks you really help me!!! I needed to use it Count(TOTAL <MYear> [Membership])