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
@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
maybe this:
=count(
{1 < STATUS = {'5'}, MODE={'T'},
Date_livraison = {">=some_date<=some_other_date"} >}
Id )
i hope it can help.
what is your key column connecting livarison and anamolie
The key is Id for table 'Livraison' and 'Id_livraison' for the table 'Anomalie'
(In my QV App they have the same name obviously)
Ok and if I want the date to be dynamic?
For example if I select a period in my calendar table (ex : from 01/06/2019 to 15/06/2019), is that possible to change some-date and some_other_date?
Maybe something like :
=count(
{1 < STATUS = {'5'}, MODE={'T'},
Date_livraison = {">=min(date)<=max(date)"} >}
Id )
The two tables cannot be linked together in qlikview if they do not have atleast one field with the same name so ID and Id_livraison cant be linking the two tables together can you please chk the Table viewer (Ctrl +T) and then see what is defining key field which is common between the two tables or share the script
you can do it as below:
=count(
{1 < STATUS = {'5'}, MODE={'T'},
Date_livraison = {">=$(=min(date))<=$(=max(date))"} >}
Id )
I think I badly explained that part. My schema with the tables was just here to facilitate comprehesion. Here is the real one :
So here :
Livraison = LDT
Calendrier = CALENDAR_ANOMALIE
Anomalie = ANOMALIES
are you trying to count Numero Shipment
Result is always 0, with or without selection.
Here is the code with real attributes names :
=count({1 < LDSTAT = {'5'}, LDMODE={'T'},SDDATE = {">=$(=min(date))<=$(=max(date))"} >}NUMERO_SHIPMENT )