Count if all field are blank??

[nburton78]

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!

[ogautier62]

Hi

What are you expecting?

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

Regards

[vishsaggi]

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]

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]

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 · Accepted solution]

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]

Thank you that worked.