Hide Null Values in pivot table won't work. Unchecked all "include null-values"-boxes

[Dominik_Keller]

Hello community,

I'm facing a problem that I just can't solve.
I have a pivot table in which I want to display 3 expressions in two dimensions. The first dimension (LSG) is a fixed value from the data model and the second dimension (period) is a calculated dimension to represent different time periods.
So far everything works, but what really bothers me now is the display of the zero values. Under Add-ons, the Include-Null-Values ​​option is disabled. The option is also disabled in the dimension itself. But the dimension LSG still has zero values.
It only works if I put the formulas in individual tables (Image 2). However, the goal is to achieve it in a table (Figure 1).
My guess is I would need to put a formula in the LSG dimension, I just have no idea how.
Does anyone have an idea where I made a mistake?

//Dimension Zeitraum
=Pick(Dim,
Date(Max(TOTAL MonthYear), 'MMM-YYYY'),
Date(MonthStart(Max(TOTAL MonthYear), -1), 'MMM-YYYY'),
Date(MonthStart(Max(TOTAL MonthYear), -2), 'MMM-YYYY'),
'YearToDate', 'Letztes Jahr YTD', 'Diff YTD'
)

//Umsatz
Pick(Dim,
if(sum({<MonthYear = {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"},Zeile={'1020'}>}Betrag)=0,null(),sum({<MonthYear = {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"},Zeile={'1020'}>}Betrag)),
if(sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"},Zeile={'1020'}>}Betrag)=0,null(),sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"},Zeile={'1020'}>}Betrag)),
if(sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -2), 'MMM-YYYY'))"},Zeile={'1020'}>}Betrag)=0,null(),sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -2), 'MMM-YYYY'))"},Zeile={'1020'}>}Betrag)),
if(sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},Zeile={'1020'}>}Betrag)=0,null(),sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},Zeile={'1020'}>}Betrag)),
if(sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},Zeile={'1020'}>}Betrag)=0,null(),sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},Zeile={'1020'}>}Betrag)),
if((sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},Zeile={'1020'}>}Betrag) - sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},Zeile={'1020'}>}Betrag))=0,null(),sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},Zeile={'1020'}>}Betrag) - sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},Zeile={'1020'}>}Betrag)))
)

//DB 1 Kosten
Pick(Dim,

if((sum({<MonthYear = {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<MonthYear = {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag))),

if((sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag))),

if((sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -2), 'MMM-YYYY'))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -2), 'MMM-YYYY'))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag))),

if((sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag))),

if((sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag))),

if((sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag) - sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag) - sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},DBStufe *= {1},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {1},Zeile = {'1323','1090'}>}Betrag)))
)

//DB 2 Kosten
Pick(Dim,
if((sum({<MonthYear = {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<MonthYear = {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag))),

if((sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag))),

if((sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -2), 'MMM-YYYY'))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -2), 'MMM-YYYY'))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag))),

if((sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag))),

if((sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag))),

if((sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag) - sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag))=0,
null(),
(sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(Max(Date)))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag) - sum({<Date = {"$(='>=' & Date(AddYears(YearStart(Max(Date)), -1)) & '<=' & Date(AddYears(Max(Date), -1)))"},DBStufe *= {2},Zeile -= {'1020','1090','1323'}>}Betrag)-sum({$<DBStufe *= {2},Zeile = {'1323','1090'}>}Betrag)))
)

[E_Røse]

Can you try to un-check the include null-values under your LSG dimensions?

Please like and mark my answer as a solution, if it resolved your issue.

[Dominik_Keller]

Hey Elin_Røse,

thanks for your quick response! This was already unchecked. Unfortunately that's not the solution yet. 

PREVIEW

[marcus_sommer]

I'm not absolutely sure if it removed all unwanted zero/null results but it will at least simplify your approach significantly. And this is to avoid the calculated dimension. IMO they is neither needed nor sensible.

Better would be to use a native dimension from the data-model in which one date belonged to multiple dimension-values. This could be reached with The As-Of Table - Qlik Community - 1466130.

Further I suggest to construct all the other P&L parts in appropriate dimensions - on an atomic level as well as in various grouping-structures and depending on the data and requirements also with an as-of-table logic, for example to consider different logic between companies and/or countries and/or changes between the years.

This means all logic belonged to the data-model so that the user just select year + company + kind of view and the appropriate dimension-values are shown and the expression would be simply: sum(Value). It's not necessary to hard-code everything within the UI by copying logic and data which already exists within your financial systems.

[Dominik_Keller]

Hi Marcus_summer,

thank you for your feedback too!
The criticism of the quick and dirty solution of calculating the values ​​in the UI is absolutely fair and appropriate. In connection with this chart it was the only solution I found so far.

Your hint with the as-of-table was good, but I still haven't found the solution in it that I need to display the last 3 months, year-to-date, previous ytd and the difference in one dimension.

In addition to the not really nice solution, I acutely see my problem in the LSG dimension. I've also tried it with an aggregated dimension. I found that if there are no values ​​for the columns, they disappear (I just created a formula as a master item to test).

=aggr(if(isnull(DB1OpsBoard),LSG),LSG)It just has no effect on the lines.

[marcus_sommer]

Instead of querying isnull(DB1OpsBoard) you may try len(trim(DB1OpsBoard)) because isnull() is only by a real NULL working and not by empty/missing values.

[Dominik_Keller]

Hey, thanks but that wasn't either the solutions.

Sometimes you can't have it all 😅 Maybe i have to accept that this approach won't work that way