topic Show all columns (Dimensions) in a straight table in QlikView

Show all columns (Dimensions) in a straight table

scottc00 — Sun, 14 Aug 2022 19:50:10 GMT

I have a horizontal straight table. There are two dimensions, Year and Month. These are the column headings.

The rows I want to show are Open Work Items and Closed Work Items.

I have filters for Sr Manager, Manager, Employee.

If no filters are selected, all columns are shown, because there is always data every month. However, when I select down to the manager and employee level, columns start to disappear. For example, If an employee started 3 months ago they will not have any data prior to that month, and so the columns have no data to display and so they disappear.

I need to display all months and years ALWAYS. Is there any way to do this?

Thanks

Re: Show all columns (Dimensions) in a straight table

Digvijay_Singh — Mon, 15 Aug 2022 03:04:44 GMT

Can you share the visual of your table? if you got some measures, try adding '+Sum({1}0)' in your measures. It helps in some situations so worth trying.

Re: Show all columns (Dimensions) in a straight table

scottc00 — Tue, 16 Aug 2022 11:42:38 GMT

Thanks Digvijay for the reply. I tried your solution. All it did was add a month that doesn't yet exist in the data (August 2022). I have also tried a lot things to try to add a one to the data.

If(Count([Field])=0,1,Count([Field]))

If(IsNull(Count([Field])),1,Count([Field]))

Etc.

I also tried many combinations of checking and unchecking

Suppress when value is Null (Dimensions)
Show All Values (Dimensions)
Suppress Zero-Values (Presentation)
Suppress Missing (Presentation)

No luck so far. Maybe I just haven’t hit on the right combination.

Is there really no-way in Qlikview to build a table that just shows all the columns (Dimensions) even if they are zero? That seems crazy to me.

Re: Show all columns (Dimensions) in a straight table

Digvijay_Singh — Tue, 16 Aug 2022 14:10:50 GMT

Do you have master Calendar table having all the Month Years present in that? That will help to show dimension values even if its not present in your fact table.

Re: Show all columns (Dimensions) in a straight table

scottc00 — Tue, 16 Aug 2022 17:25:06 GMT

Thanks Digvijay. I'll try that. I'll get back to you.

Re: Show all columns (Dimensions) in a straight table

scottc00 — Tue, 16 Aug 2022 19:30:52 GMT

Thanks for all your help Digvijay. Here’s how I resolved it. I added an expression that always has data when no selection is made and used set analysis to ignore the selections =Count({1<[Field]… and then hid all the columns I don’t want to display using the Hide Column radio button on the Presentation tab