Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Count blanks in Pivot - Show in text box

Hi,

Is there a way to count blanks in a pivot and display the answer in a text box.

In my screenshot below, how do I count the blanks (those circled in blue) ?

My formula for the expression is as follows

if(Sum(Sales) <=0, chr(10007),' ')

Kind regards,

Nayan

pivot 2.JPG

4 Replies
Highlighted
Champion
Champion

Re: Count blanks in Pivot - Show in text box

Hi,

for foreign_exchange measure for example, in textbox;

(supposing that your foreign_exchange expression is sum(foreign_exchange)

=aggr(sum({<foreign_exchange={"=len(trim(foreign_exchange))=0"}>}foreign_exchange),Parent,Legal_entity)

Highlighted
Creator
Creator

Re: Count blanks in Pivot - Show in text box

Hi Youssef

Thank you for your reply.  I've tried that , but does not work.  The screenshot that i have given is a pivot table and there is only one formula.  The headings that you see are values of a horizontal dimension in a pivot.

Kind regards

Nayan

Highlighted
Champion
Champion

Re: Count blanks in Pivot - Show in text box

so try this:

=aggr(sum({<legal_entity={"=len(trim(legal_entity))=0"}>}leagl_entity),Parent)

Highlighted
Creator
Creator

Re: Count blanks in Pivot - Show in text box

Thank you Youssef.  Will try and let you know.

Kind regards

Nayan