Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
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
Yes, I'm trying this right now :
=count({1 < LDSTAT = {'5'}, LDMODE={'T'},SDDATE = {">=$(=min(date))<=$(=max(date))"} >}NUMERO_SHIPMENT )
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.
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)
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
I just tried this solution, LDT was not linked to any other table but I get 0 as result
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.
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.
@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
Awesome, I am glad you were able to figure this out.