Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
nburton78
Creator
Creator

Count if all field are blank??

Hello

I’m new to all this so be nice please.  I am working on making a chart for my data.  I am struggling with the expression.  What I want to do is show a count of all parts that are blank in all 4 columns sales columns (C-F) by region.  I can do it for one column, but I can’t seem to write the correct expression to check all columns.  In Excel this is how I would write it “=COUNTIFS(C2,"",D2,"",E2,"",F2,"")”

Heres what I have so far:

=Count ({< Part_Number={"=Len(trim(Most_Recent_Order_Date))=0"}>} Part_Number)

I attached an excel example of what I"m trying to do.  Please Help!

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

Oh ok. I just gave you the solution based on what i have seen in your excel sheet. Just use the same expr in your bar chart. Did you try? If not can you share your sample qvw file to look into? So when you say they are coming from different tables, can you show how your data model looks like?

may be try like below in your expr with Region as your Dimension.

= Sum(IF( Len(Trim(Most_Recent_Order_Date&Most_Recent_Open_Order_Date&Most_Recent_Work_Order_Date&Most_Recent_Work_Order_Open_Order_Date))=0, 1, 0))

View solution in original post

6 Replies
ogautier62
Specialist II
Specialist II

Hi

What are you expecting?

4colums? Or 1 column with total blank or 1 if at least one blank?

Regards

vishsaggi
Champion III
Champion III

Try this?

CountPart:

LOAD [Part number],

     REGION,

     Most_Recent_Order_Date,

     Most_Recent_Open_Order_Date,

     Most_Recent_Work_Order_Date,

     Most_Recent_Work_Order_Open_Order_Date,

     IF( Len(Trim(Most_Recent_Order_Date&Most_Recent_Open_Order_Date&Most_Recent_Work_Order_Date&Most_Recent_Work_Order_Open_Order_Date))=0, 1, 0) AS NoOfSales

FROM

[Qlikview example.xlsx]

(ooxml, embedded labels, table is Sheet1);

Then just use bar chart with REGION as Dimension and

Expr: Sum(NoOfSales)

nburton78
Creator
Creator
Author

no columns, I want to count the total by region with no sales.  I am trying to write this as an expression in a bar chart but I can't seem to write it so that it checks to see if all columns are blank then count the part number. 

nburton78
Creator
Creator
Author

But each column is listed as an individual SQL load each coming from a different table like this

" SQL SELECT distinct "Short_Item_Number__SDITM" as [Short_Number],

  Max("Order_Date__SLUPMJ") as [Most_Recent_Order_Date]

  from DWH_V1.dbo.V_SalesHistory

where Order_Type__SDDCTO <> 'SQ'

Group by Short_Item_Number__SDITM; "

vishsaggi
Champion III
Champion III

Oh ok. I just gave you the solution based on what i have seen in your excel sheet. Just use the same expr in your bar chart. Did you try? If not can you share your sample qvw file to look into? So when you say they are coming from different tables, can you show how your data model looks like?

may be try like below in your expr with Region as your Dimension.

= Sum(IF( Len(Trim(Most_Recent_Order_Date&Most_Recent_Open_Order_Date&Most_Recent_Work_Order_Date&Most_Recent_Work_Order_Open_Order_Date))=0, 1, 0))

nburton78
Creator
Creator
Author

Thank you that worked.