topic Re: Eliminate Null Dimensions in QlikView

Eliminate Null Dimensions

Fri, 26 May 2017 17:05:14 GMT

I created a bar graph that is a sum by month of the current month plus the previous 12 months. Now I want to remove the months that have no values. I already tried going into PROPERTIES => DIMENSIONS => And Checking the box that says Suppress When Value Is Null. This did not work. Any idea how to remove the blank months?

Formula:

=NUM(SUM({$<[Month End Date]={">=$(=MonthStart(AddMonths(Max([Month End Date]),-12))) <$(=MonthEnd(Max([Month End Date]),+1))"}, [Fact Setup ID]={"576817","576818","576819","576820","576821","401258"}>} [Metric Value]), '###,##0')

Thanks,

Seth

Re: Eliminate Null Dimensions

sunny_talwar — Fri, 26 May 2017 17:09:14 GMT

Maybe check 'Suppress Zero Value' on the presentation tab

Re: Eliminate Null Dimensions

Gysbert_Wassenaar — Fri, 26 May 2017 17:09:24 GMT

You're using two dimensions. In that case space will be reserved for each month even if there is a value for a month in only one of the two years. If you don't want that then you must use a single dimension. Something like =Year & '-' & Month

Re: Eliminate Null Dimensions

Fri, 26 May 2017 17:23:10 GMT

I also have the Suppress Zero Value checked in the presentation tab.

I want the year and month as 2 separate dimensions so it shows the summed values in chronological order. If I remove the year dimension then the chart orders the months as Jan - Dec and rolls up the 1 overlapping month into a single summed value. Is there no way around this? This is something basic that can be done in an excel graph.

Re: Eliminate Null Dimensions

Fri, 26 May 2017 17:25:04 GMT

I see what you are saying creating a "Year"&-&"Month" field. I just don't think that looks as nice. Is that the only way?

Re: Eliminate Null Dimensions

Gysbert_Wassenaar — Fri, 26 May 2017 17:32:33 GMT

Yes.

Re: Eliminate Null Dimensions

Fri, 26 May 2017 17:41:34 GMT

So if I use the 1 dimension how do I sort it so the months are in chronological order? It's like when I combined the month and year it can only see the month as text and wants to put it in alpha order. I would prefer not to use the month number unless that is my only option. I have tried a few different sort unsuccessfully. Thanks.

Re: Eliminate Null Dimensions

Gysbert_Wassenaar — Sat, 27 May 2017 14:03:16 GMT

You seem to have a [Month End Date] field. You can use that as dimension and format it a bit: Date([Month End Date], 'YYYY-MMM'). If you sort that dimension numerically you'll get the sort order you want.