Show rows depending on field values

[fgirardin]

Hello,

Using views from our ERP, I'm trying to make a table that shows the state of our sales

I use documents informations to do so (customer name, item, quantity, price, document type, invoice N°, date, task, ...)

Here is my table

Each item should have 3 documents: Order / Shipment / Invoice

These 3 documents have each a state that I get from the same field (DOC.OPERATION)

It is either DBCD / DBBL / DBFA

Using this table I can check that each order has each document (order, shipment confirmation and finally invoice)
As you can see on my table, I show each state on a different column (using "IF" condition)

What I'd like to do is to filter ONLY items that have a shipment confirmation (DBBL) but NO invoice (DBFA)
This way I can easily check if the customer has received our invoice (I don't need to check if it's paid, only that the process is complete --- Order > Shipment > Invoice)

As the status field is the same I can't find a way to show only items with DBBL state and no DBFA

I tried to use "COUNT", "ONLY", ... but so far it's not working

If someone have any idea to achieve what I need, let me know

Thank you

FG

[Sergey_Shuklin]

Hello, Fabien!

You can try a set analysis to solve this task. Something like this:

sum({<State={'DBBL'>}[Unit Price])

or sum({<State=-{'DBFA'>}[Unit Price]) if you want to exclude DBFA state.

[fgirardin]

Hello Sergey,

Thank you for your help.

If I use the 1st set analysis, I only see the document and price if there is a shipping document (DBBL)

What I would like to see is ONLY the items that have a shipping document (DBBL) but NO invoice (DBFA)
If I have 2 items as such:

ITEM     DOC N°     Doc.DATE     ORDER     SHIPPED     INVOICE     PRICE

Item1     CD0001     01.01.17         DBCD       -                   -                  10

Item1     BL0002      01.02.17         -                DBBL           -                  10

Item1     FA003        02.02.17         -                -                  DBFA           10

Item2     CD0004     01.01.17         DBCD       -                   -                  10

Item2     BL0005      01.02.17         -                DBBL           -                  10

I would like to show only (in another table for example)

Item2     CD0004     01.01.17         DBCD       -                   -                  10

Item2     BL0005      01.02.17         -                DBBL           -                  10

I need to fulfill 2 conditions:

A) Must have an Shipping Document (DBBL)

B) Must have NO Invoice (DBFA)

Thanks again

[Digvijay_Singh]

This should work -

Dimension - All info fields

Expression as -

Only({<ITEM={"=len(Aggr(concat(INVOICE),ITEM))=0 and len(Aggr(concat(SHIPPED),ITEM))>0"}>}PRICE)

[fgirardin]

Hello Digvijay,

Thank you for your help

I can't make it work using your example

Note that the columns ORDER, SHIPPED & INVOICE get data from the same field "DOC.OPERATION"

I use IF statement to create my three columns as 3 separate dimensions >>>

=if(DOC.operation='DBCD', Doc.operation , '')

=if(DOC.operation='DBBL', Doc.operation , '')

=if(DOC.operation='DBFA', Doc.operation , '')

If I try to adapt your expression >

Only({<ITEM={"=len(Aggr(concat(DOC.Operation='DBFA'),ITEM))=0 and len(Aggr(concat(DOC.Operation='DBBL'),ITEM))>0"}>}PRICE)

It does not work

I could replace the operation status by any letter or number. I did this at first to be able to see all operation for each item

I'm starting to think that it wasn't a good idea

Thanks again for your help.

[fgirardin]

I recreated my table and changed the way I show datas

Here it is:

Fields are:

Ref.        |      Item       |  Operation  |   Doc. N°   |  Doc.Date  |  Qty | Unit Price | Misc | Total Price

5011635...   BCTE-325      DBCD      17/50245     14.03.2017    40           15        00          600

                                       DBBL       17/30331      27.03.2017   40           15         00         600

                                       DBFA       17/402663    27.03.2017    40          15         00          600

I need to show only ITEMS that have DBBL (shipping document) but no DBFA (invoice)

Thank you again for your precious help !

FG

[Digvijay_Singh · Accepted solution]

This one works with your new data -

I used below sample from your data, I assumed that empty invoice means no row with DBFA, as below for item 326.

So as per below sample only item should show is 326.

-

Load * inline [

Ref,Item,Operation,Doc. N°,Doc.Date,Qty,Unit Price,Misc,Total Price

5011635,  BCTE-325,      DBCD,      17/50245,    14.03.2017,    40,          15,        00,          600

5011635,  BCTE-325,      DBBL,      17/30331,      27.03.2017,  40,          15 ,        00,        600

5011635,  BCTE-325,      DBFA,      17/402663,    27.03.2017 , 40,          15 ,        00,        600

5011639,  BCTE-326,      DBCD,      17/50245,    14.03.2017,    40,          15,        00,          700

5011639,  BCTE-326,      DBBL,      17/30331,      27.03.2017,  40,          15 ,        00,        700

5011641,  BCTE-327,      DBCD,      17/50245,    14.03.2017,    40,          15,        00,          600

5011641,  BCTE-327,      DBBL,      17/30331,      27.03.2017,  40,          15 ,        00,        600

5011641,  BCTE-327,      DBFA,      17/402663,    27.03.2017 , 40,          15 ,        00,        600

];

Expression used for Total price as -

Only({<Item={"=Count({<Operation={'DBBL'}>}Operation)= 1 and Count({<Operation={'DBFA'}>}Operation)=0"}>}[Total Price])

Rest of the fields used as Dimensions

[fgirardin]

Sorry for my late response, I was quite busy on other tasks...

Thank you for your help

I tested your file to see if there was any problem with regional settings on my end and it works fine.... sadly it doesn't work with my datas

If I use your example with my fields >

Only({<ART.Article_cle_principale={"=Count({<DOC.Operation_predefinie_code={'DBBL'}>}DOC.Operation_predefinie_code)=1 and Count({<DOC.Operation_predefinie_code={'DBFA'}>}DOC.Operation_predefinie_code)=0"}>}[LIG.Montant_HT])

                                                                 Item     Operation     Total Price

(I only switched Unit Price and 1 other expression into Dimensions)

It says the formula is correct but it does not show ANY price...

I tested with this formula:

Only({<ART.Article_cle_principale={"=Count({<DOC.Operation_predefinie_code={'DBFA'}>}DOC.Operation_predefinie_code)=1"}>}[LIG.Montant_HT])

In this case it does not show the price for "BCTE-382_A" as this item only has "DBCD" (order) and no "DBFA" (Invoice)

However, if I try the same formula but with "=0" at the end, nothing shows (all prices are empty)

Any ideas ?

Thanks a lot

FG

[fgirardin]

I think I found out what was the problem.
Actually we don't have a lot of item without invoices. The only we have are at "00.00" because we are waiting to add a price....
I will ask the person creating the invoices to add 1.- as total if we don't have prices so I can see them on my report

Or is there a way to tell QlikView that 00.00 is not a null value ?

Thanks again