Showing a Column based on a Conditional Expression

[#1]

I have a straight table. In that table I have 12 columns for the 12 months of the year. What I want to be able to do is set up a conditional expression so the column shows up if a value in that month exists. Basically, I have the logic built for the whole year and I want the columns to populate only when data for that month exists.

I know I can go in and check the "Enable" box in the "Expressions" tab but I want a way to do that automatically with a conditional statement so I dont' have to do it manually.

thanks,

rob

[#2]

How is your data set up? In QlikView, if there is no data, then those months shouldn't show up (unless Suppress Zero-Values is unchecked).

From your description, it almost sounds like you are creating separate expressions for each month. Is that what you are doing? If so, you may want to consider adding the Month as a Dimension and using a Pivot Table with the Months across the top.

If you are using separate expressions, you could probably use the Conditional Show under the Presentation tab. It would be something like:

If(Sum(Sales)>0, 1, 0)

[#3]

I actually have dimensions on that table as well. Below is an example on how the data is set up. I want to be able to track those dimensions over time, but I don't want Oct. '09 Leads, Nov. '09 Leads, or the Dec. '09 Leads columns to show up until I get data for those months.

Type Publication Aug. '09 Leads Sep. '09 Leads Oct. '09 Leads Nov. '09 Leads Dec. '09 Leads

A 1 100 50 0 0 0

A 2 200 60 0 0 0

A 3 300 80 0 0 0

rob

[#4]

But are all those Leads expressions separate? Does your Expressions tab look like this:

If so, then go over to the Presentation tab and use the Conditional Show.

[#5]

Yes, it looks something like the screen print you attached. I'm using version 8.5. I'm not seeing a Conditional Show on the Presentation tab. Is that a version 9.0 option?

[#6]

Yes, sorry, that looks to be a new feature to version 9. I couldn't figure out a way to suppress that column in 8.5. I even tried setting the value to null for everything and it still showed up.

I'm going to try to put together a quick pivot example that should do what you want. I'll build it in 8.5.

EDIT 2: My first edit didn't show. I made a quick pivot, but it's not too exciting. If a month is not available, it will not show up. See if something like that would work for you.

[#7]

NMiller,

Thanks for your help. I have a copy of version 9.0 on a seperate server and was able to use the conditional area on the Presentation tab to get exactly what I was after.

If anyone else runs across this post, this is what I put in the conditional area on the Presentation tab in version 9.0:

I placed the below code in for each of the 12 month I had expressions for (note the Month and CYTD (current year to date. this field is a 1 or 0) are values in my script). If one of the months, for the current year, has a value then the column will show, otherwise the column remains hidden.

=

if(count(if(CYTD and Month='Jan',RecordID))>0,1,0)

=

if(count(if(CYTD and Month='Feb',RecordID))>0,1,0 )

etc...

rob

[prieper]

Hi,
although everything is possible, would propose to come back to a normalization of the data, which might be a table in the format Dimension(s), Item, Value. To enable an automatic generation there is the CROSSTABLE-functionality available.

HTH
Peter

[#9]

From the formula you posted (if(count(if(CYTD and Month='Jan',RecordsID))>0,1,0), it seems like the month value is stored in the same column in the database "Month".

If this is the case, a pivot table would be a better option (instead of having to put 12 months in the expression, with conditional formula, what you need to do is to bring in the "month" column into the dimension), just drag across the month column to top right of the pivot table. QV would eliminate the fields without any value for you. It will just look like a crosstab query in MS Access.