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: 
YoussefBelloum
Champion
Champion

Set analysis with networkdays problem

Hi everyone !

I'm struggling on make my expression works...

Below is my data structure:

Date AnnulationDate CréationDate Début EffetDate EmissionN° Quittance
04/01/201619/11/201501/01/201601/01/20162160100003
04/01/201604/01/201601/01/201504/01/20162160100192
04/01/201604/01/201601/01/201504/01/20162160100194
04/01/201604/01/201601/01/201504/01/20162160100209
04/01/201604/01/201619/01/201504/01/20162160100189
04/01/201604/01/201603/07/201504/01/20162160100210
05/01/201630/11/201501/01/201601/01/20162160100083
06/01/201604/01/201601/01/201604/01/20162160100300
06/01/201604/01/201601/01/201604/01/20162160101076
11/01/201604/01/201601/01/201604/01/20162160100263
11/01/201604/01/201601/01/201604/01/20162160100266
11/01/201604/01/201601/01/201604/01/20162160100665
11/01/201604/01/201601/01/201604/01/20162160100666

 

I'm calculating the avg of deadlines for issuing receipts with some conditions, with the (working) expression below:

=avg({<Equipes={'Equipe CR'},Année={$(=Max(Année))},Mois={"<=$(=max(Mois))"}>}aggr( if([Date Emission]>[Date Début Effet], NetWorkDays([Date Début Effet],[Date Emission])),[N° Quittance]))

I'm using a line chart and here is the result:

IMAGE1.png

everything is working fine until I try to filter on month field (Month field is a calendar field, my table above is linked with the calendar field with the date field "Date création"), when I select a value on month, example "July", I need the lines to show from January TO July.. but the lines are showing this:

IMAGE2.png

PS: THE " Année={$(=Max(Année))},Mois={"<=$(=max(Mois))"} "portion of my set analysis works perfectly fine on all the other charts of my app, except the one described above...

One last note: it seems that the set analysis partially works, because the chart is filtered from January to July when I select July on the field Month, but something is missing to show all the values of the line...

Thank for your help

 

Youssef B.

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

You might need to ignore selection in your fields inside the Aggr() function expression

=Avg({<Equipes = {'Equipe CR'}, Année = {$(=Max(Année))}, Mois = {"<=$(=max(Mois))"}>}Aggr(
    
    If(Only({1} [Date Emission]) > Only({1} [Date Début Effet]), Only({1} NetWorkDays([Date Début Effet], [Date Emission])))

, [N° Quittance]))

I have ignored all selections using {1}, but you can ignore selection in certain fields based on your requirement. 

View solution in original post

8 Replies
sunny_talwar

You might need to ignore selection in your fields inside the Aggr() function expression

=Avg({<Equipes = {'Equipe CR'}, Année = {$(=Max(Année))}, Mois = {"<=$(=max(Mois))"}>}Aggr(
    
    If(Only({1} [Date Emission]) > Only({1} [Date Début Effet]), Only({1} NetWorkDays([Date Début Effet], [Date Emission])))

, [N° Quittance]))

I have ignored all selections using {1}, but you can ignore selection in certain fields based on your requirement. 

YoussefBelloum
Champion
Champion
Author

Thank you @sunny_talwar 

I was trying to post the solution I found too, I replaced the if with a set analysis and used {1}:

=avg({<Equipes={'Equipe CR'},Année={$(=Max(Année))},Mois={"<=$(=max(Mois))"}>}aggr(sum({<[Date Création]={"=only({1}[Date Emission])>only({1}[Date Début Effet])"},Mois={"<=$(=max(Mois))"}>} NetWorkDays([Date Début Effet],[Date Emission]))),[N° Quittance]))

 

sunny_talwar

Does this expression really works? Seeing few errors in the syntax

YoussefBelloum
Champion
Champion
Author

@sunny_talwar 

I was making some tests on a test app with a slightly modified expression (without Networkdays), so the one I put above I wrote it directly on the webpage, and there is only an extra bracket (the red one):

=avg({<Equipes={'Equipe CR'},Année={$(=Max(Année))},Mois={"<=$(=max(Mois))"}>}aggr(sum({<[Date Création]={"=only({1}[Date Emission])>only({1}[Date Début Effet])"},Mois={"<=$(=max(Mois))"}>} NetWorkDays([Date Début Effet],[Date Emission]))),[N° Quittance]))

The real problem is when I tried this one on the real app, the numbers are completely different..

Is there something missing here too ?

sunny_talwar

I am not sure what exactly is the expectation... my bad with not reading your expression... what I thought was an error... was not really an error.

YoussefBelloum
Champion
Champion
Author

@sunny_talwar 

I was expecting to make the expression work with a set analysis instead the IF used on the first expression

That means, Replacing the Red part of your expression:

=avg({<Equipes={'Equipe CR'},Année={$(=Max(Année))},Mois={"<=$(=max(Mois))"}>}aggr( if(only({1}[Date Emission])>only({1}[Date Début Effet]), Only({1}NetWorkDays([Date Début Effet],[Date Emission]))),[N° Quittance]))

with the green part of this expression:

=avg({<Equipes={'Equipe CR'},Année={$(=Max(Année))},Mois={"<=$(=max(Mois))"}>}aggr(sum({<Date={"=only({1}[Date Emission])>only({1}[Date Début Effet])"}>} NetWorkDays([Date Début Effet],[Date Emission])),[N° Quittance]))

I thought it is working, but it is not...

Is there something missing on the expression with the green part ?

thank you

sunny_talwar

May be try this... but you are not getting rid of the if statement... you are just moving it within your set analysis...

=Avg({<Equipes = {'Equipe CR'}, Année = {$(=Max(Année))}, Mois = {"<=$(=max(Mois))"}, [N° Quittance] = {"=Only({1} [Date Emission]) > Only({1} [Date Début Effet])"}>} NetWorkDays([Date Début Effet], [Date Emission]))

To get rid of the if statement... I would say that try to create a flag by running this in the script

If([Date Emission]) > [Date Début Effet], 1, 0) as TrueFalseFlag

and then this

=Avg({<Equipes = {'Equipe CR'}, Année = {$(=Max(Année))}, Mois = {"<=$(=max(Mois))"}, [TrueFalseFlag] = {'1'}>} NetWorkDays([Date Début Effet], [Date Emission]))
YoussefBelloum
Champion
Champion
Author

You answered all my questions. Thank you for your time 🙏