topic Re: Count if all field are blank?? in QlikView

Count if all field are blank??

nburton78 — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Count if all field are blank??

ogautier62 — Thu, 05 Jul 2018 15:59:11 GMT

What are you expecting?

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

Regards

Re: Count if all field are blank??

vishsaggi — Thu, 05 Jul 2018 16:40:15 GMT

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)

Re: Count if all field are blank??

nburton78 — Thu, 05 Jul 2018 18:52:13 GMT

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.

Re: Count if all field are blank??

nburton78 — Thu, 05 Jul 2018 18:55:30 GMT

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; "

Re: Count if all field are blank??

vishsaggi — Thu, 05 Jul 2018 20:39:16 GMT

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))

Re: Count if all field are blank??

nburton78 — Fri, 06 Jul 2018 12:38:13 GMT

Thank you that worked.