Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
GuillaumeRUE
Contributor III
Contributor III

Ignore a complete table with set analysis

Hello,

I'm trying to calculate the number of delevries inside a QV app but I have a little problem.

Here are my tables in my QV app :

QV.png

 

So, I would like to calculate the number of deliveries without using tables 'Anomalies', 'Type' and 'Raison'.

In SQL I would do it like this :

 

SELECT count(*) as Nb_deliveries
FROM Livraison
WHERE status = 5
AND mode= 'T'
AND date_livraison >= some_date
AND date_livraison < some_other_date

 

 

But in QV I only get records where there is a link between the delivery and table 'Anomalie'.

I tried using set analysis but I can't get better than a count of the entire delivery table :

 

=count({1<STATUS={5},MODE={`T`}>}Date_livraison)

 

So I imagine if I could set the date in the expression it's ok but I don't find how to do it.

 

If someone can help me that would be perfect 🙂

Guillaume

18 Replies
GuillaumeRUE
Contributor III
Contributor III
Author

Yes, I'm trying this right now :

=count({1 < LDSTAT = {'5'}, LDMODE={'T'},SDDATE = {">=$(=min(date))<=$(=max(date))"} >}NUMERO_SHIPMENT )

agigliotti
Partner - Champion
Partner - Champion

you have to remove 1 set operarator like below:

=count( {< LDSTAT = {'5'}, LDMODE={'T'},SDDATE = {">=$(=min(date))<=$(=max(date))"} >} NUMERO_SHIPMENT )

1 operator means you take all data regardless any selection made.

anushree1
Specialist II
Specialist II

So can you please try loading Numero Shipment column again like below 

LDT:

LOad 

Field1, field2,....

Numero Shipment as Test

and then use the new Test Field which is not linked to any table in the expression like:

=count({1 < LDSTAT = {'5'}, LDMODE={'T'},SDDATE = {">=$(=min(date))<=$(=max(date))"} >}Test)

GuillaumeRUE
Contributor III
Contributor III
Author

I copied your code but I still have 0 as result.

When i just write =count( {< LDSTAT = {'5'}, LDMODE={'T'} >} NUMERO_SHIPMENT ) I get the total result so it's fine but as soon as I tried to add restriction on the date I have null result

GuillaumeRUE
Contributor III
Contributor III
Author

I just tried this solution, LDT was not linked to any other table but I get 0 as result

sunny_talwar

What is the format for SDDATE field? Also, how is Date linked to SDDATE? In the expression above you use date instead of Date (as seen from your date model)... is that just a typo? Qlik is case sensitive so make sure to use the correct casing.

agigliotti
Partner - Champion
Partner - Champion

let's try replacing real value to variable ex. 

=count( {< LDSTAT = {'5'}, LDMODE={'T'},SDDATE = {">=01/06/2019<=15/06/2019"} >} NUMERO_SHIPMENT )

to see what you get as result.

GuillaumeRUE
Contributor III
Contributor III
Author


@sunny_talwar wrote:

What is the format for SDDATE field? Also, how is Date linked to SDDATE? In the expression above you use date instead of Date (as seen from your date model)... is that just a typo? Qlik is case sensitive so make sure to use the correct casing.


Thank you you pointed my mistakes!

In fact, SDDATE was not in the same format as Date AND I wrote 'date' instead of 'Date'.

 

Then, I tried :

=count( {< LDSTAT = {'5'}, LDMODE={'T'},SDDATE = {">=$(=min(Date))<=$(=max(Date))"} >}NUMERO_SHIPMENT )

 

but result was still false, so I added '1' and I finally got something but the number was too big. The reason was QV used all 'NUMERO_SHIPMENT' so I just added a DISTINCT and now I have the good result. So my final expression is : 

=count( {1< LDSTAT = {'5'}, LDMODE={'T'},SDDATE = {">=$(=min(Date))<=$(=max(Date))"} >} DISTINCT NUMERO_SHIPMENT )

 

Thank you very much @sunny_talwar  @agigliotti  and @anushree1  for your help.

 

Guillaume

sunny_talwar

Awesome, I am glad you were able to figure this out.