Grouping of same number in Qlik Sense

[vsap2000]

Hi All,

I am new to Qlik Sense, and trying to achieve group of invoice number with nodes. I have field Invoice #, Sub Invoice # ,Main Invoice# which is as shown below:

Invoice #   Sub Invoice#   Main Invoice #         Inv Flag           Count Inv

1                   1                          null                              Group Inv        1

1                  1.1                     1                                    Group Inv

1                 1.1.2                  1                                   Group Inv

2                2                          null                               Non Grp           1

3               3                          null                               Non Grp           1

4               4                         null                              Group Inv          1

4              4.1                      4                                    Group Inv

4             4.2                     4                                      Group Inv

4             4.3                    4                                       Group Inv

5            5.1                    5                                       Group Inv          1        Shows only 1 but still it should fall under Grp Inv.

6           6                       Null                                    Non Grp           1

I need to make a Filter pane so that user can select between Group and Non Group invoice. 

Any help is appreciated.

Thanks in advance.

[Gysbert_Wassenaar]

Drag a Filter Pane onto the sheet canvas and then add the field Inv Flag as dimension to the filter pane.

[vsap2000]

Thanks for your reply, sorry I was not clear about my filter pane question, actually I want to Create Inv Flag dimension with those 2 elements, Group Inv and Non Group Inv, I don't know how to group  them.

Also I like to create Inv Count measure for that I created Inv Count measure column and use following expression:

I tried Count(Aggr(([Sub Invoice #]),[Invoice #])) 

but issue is it is not considering invoices which had Sub Invoice #.  For example 1, 1.1, 1.1.2 got neglected, and these 3 should fall under Group Inv category.

Hope I was able to make it clear.

Thanks!

[Gysbert_Wassenaar]

Perhaps with a load script like this:

tmp:
LOAD
    [Invoice #], 
    [Sub Invoice#],   
    [Main Invoice #],
    [Inv Flag],    
    [Count Inv]	
FROM
    ...source...
	;

JOIN (tmp)

LOAD
    [Invoice #],
    If(group_count>0,'Group Inv','Non Grp') as [Inv Flag]
    ;
LOAD
    [Invoice #],
    count([Sub Invoice#]) as group_count
RESIDENT
    tmp
GROUP BY
    [Invoice #]
    ;
	
	
Data:
LOAD
    *,
    If([Invoice #]<>Peek([Invoice #]),1) as [Count Inv]
RESIDENT
    tmp
ORDER BY
    [Invoice #], 
    [Sub Invoice#]
    ;
	
DROP TABLE tmp;	

[vsap2000]

@Gysbert_Wassenaar  thanks for reply, I am not allowed to change data model or script.  I was trying to achieve this in worksheet by writing expression in Filter Pane or any suggestion is welcome. 

Is this achievable in front end rather than back end.

Thanks.

[vsap2000]

Any help is appreciable.

Thanks.