Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with null values in total in adhoc report

Hi,

I have made an ad hoc report with 12 dimensions and 6 measures for the users to select from, along with a dynamic total (if the user selects dimension 1,3 & 4 then the total is displayed as measure 1+3+4  as so on).

the problem i am facing is that when the measures values are mapped against a null value in dimension, the value is shown in the individual columns but not in the dynamic total. please find a screenshot below and also attached, along with the expression I am using.

screenshot.png

Notice the value in Last Row (#N/A)

The total 1 expression is

=

if(SubStringCount(Concat(ID2,'|'),'M6')>0,[Sales Till Last Cutoff],0)

+

if(SubStringCount(Concat(ID2,'|'),'M2')>0,[In Progress],0)

+

if(SubStringCount(Concat(ID2,'|'),'M3')>0,[Credit Hold],0)

+

if(SubStringCount(Concat(ID2,'|'),'M4')>0,[Complaince Hold],0)

+

if(SubStringCount(Concat(ID2,'|'),'M5')>0,[Error],0)

+

if(SubStringCount(Concat(ID2,'|'),'M1')>0,[Invoiced Since Last Cutoff],0)

with Sum of Rows as Total Mode

The Total 2 has same expression as the Total 1, but with Expression Total as Total Mode. This is considering the values in the total but not displaying them.


Total 3 is column(1)+column(2)+column(3)+column(4)+column(5)+column(6)

I want the values to display in the last row in Total 1 or Total 2.

Please help.

Regards

Apoorv Jain

0 Replies