sum for if expression

[rathorep]

I have below expression:

//sum(Aggr(

if( if( if (NetWorkDays(date(Above(TOTAL [Field Modified Date],1),'DD/MM/YYYY'),Date([Field Modified Date],'DD/MM/YYYY')-1) < (floor(sum(Aggr( if ("Field Modified Group Code"=$(vFieldModifiedGroupCode), Interval(sum(Aggr(Interval(Rangemax(Interval((Above(TOTAL [Field Modified Date],1) - [Field Modified Date])*(-1), 'd.hh.mm'),0),'d.hh.mm'), [Win@proach Ticket Number],([Field Modified Date], (NUMERIC)))),'d.hh:mm'),0 ),[Win@proach Ticket Number],([Field Modified Date],(NUMERIC)))))) , ((( (interval(sum(Aggr( if ("Field Modified Group Code"=$(vFieldModifiedGroupCode), Interval(sum(Aggr(Interval(Rangemax(Interval((Above(TOTAL [Field Modified Date],1) - [Field Modified Date])*(-1), 'd.hh.mm'),0),'d.hh.mm'), [Win@proach Ticket Number],([Field Modified Date], (NUMERIC)))),'d.hh:mm'),0 ),[Win@proach Ticket Number],([Field Modified Date],(NUMERIC)))),'d.hh:mm'))) - interval( (round(sum(Aggr( if ("Field Modified Group Code"=$(vFieldModifiedGroupCode), Interval(sum(Aggr(Interval(Rangemax(Interval((Above(TOTAL [Field Modified Date],1) - [Field Modified Date])*(-1), 'd.hh.mm'),0),'d.hh.mm'), [Win@proach Ticket Number],([Field Modified Date], (NUMERIC)))),'d.hh:mm'),0 ),[Win@proach Ticket Number],([Field Modified Date],(NUMERIC)))))) - (NetWorkDays(date(Above(TOTAL [Field Modified Date],1),'DD/MM/YYYY'),Date([Field Modified Date],'DD/MM/YYYY')-1)),'d.hh.mm') )) , (if ("Field Modified Group Code"=$(vFieldModifiedGroupCode), (sum(Aggr(Interval(Rangemax(Interval((Above(TOTAL [Field Modified Date],1) - [Field Modified Date])*(-1), 'd.hh.mm'),0),'d.hh.mm'), [Win@proach Ticket Number],([Field Modified Date], (NUMERIC))))),0 )))>'2.75' and Remarks <>'Exception','Exceeded Target',if((([Field Modified Group Code]<>$(vFieldModifiedGroupCode)) and Remarks='Exception'),'NA','Meet Target')) ='Exceeded Target', count( {<[Field Modified Group Code]={$(vFieldModifiedGroupCode)} , [Record Type] = {'IR'}, Severity={'Medium'}>}distinct [Win@proach Ticket Number]) ,0)

//,[Win@proach Ticket Number]))

Result I am getting is:

Win@proach Ticket Number	Field Modified Date	With the Expression shared
197264	7/24/2020 14:38	0
197264	7/27/2020 7:07	0
197264	7/30/2020 13:21	1
197264	7/30/2020 14:27	0
197264	8/18/2020 23:12	0
203257	11/10/2020 9:18	0
203257	11/10/2020 9:22	0
203257	11/25/2020 13:07	1
203257	11/26/2020 12:39	0
203257	11/26/2020 13:17	0
203257	12/2/2020 0:11	0

Now i need to calculate sum on top of this, so when i am un commenting first and last bold lines, Its giving 0 in full column and total also is coming 0. Sum should be 2 Could any one help me urgently please.

[tresesco]

I would not go much into the expression (such a huge one !). Try a quick possible fix. Include NoDistinct in aggr() like:

Sum(Aggr( NoDistinct (...

[rathorep]

Hello, Thanks for quick reply, But I did not get correct sum with nodistinct.

[tresesco · Accepted solution]

Well, one more check; are you using [Field Modified Date] as a dimension/field in the above table? If so, you might need to include the same field in aggr() as well, like:

Sum(Aggr(.... ) ,[Win@proach Ticket Number], [Field Modified Date]))

[ArnadoSandoval]

Hi @rathorep 

Who will support that huge expression in your code? next week or next month, it looks like a dish of spaghettis; Would you help us and yourself by formatting that expression? I tried, even with 2 meters of video it is a daunting task! It will take me about 3 days to simplify your code.

hth 

[rathorep]

Hi @ArnadoSandoval 

I completely agree that the code is very huge and it should be a simplified one and formatted one. but the reason of it is we can not substract weekends between 2 dates(please let me know if we have direct function to the number of weekends). I am sorry to put this much huge code. I understand its very messy 😞

[rathorep]

Hi @tresesco 

I had tried this one before but not sure what i was doing wrong but you are amazing , it gave me the correct sum now ..Thanks a ton !!!! once again sorry to share this much long code...

If we can directly have the number of weekends between 2 dates please let me know. it will help me to shortened my code ...

[ArnadoSandoval · Accepted solution]

Hi @rathorep 

Perhaps there is a better way to do it, but you should share with us more about your data model and the rules to count the weekends between two dates, I found this topic ¿How to count Saturdays in date range?  I even wrote a simple QVF application exploring the WeekDay function potential,  the expression you posted does not implement the WeekDay function.

Please take a look at the attached QVF file.

[Ksrinivasan]

hi,

in load script, you can add this if function

(If(WeekDay("YourDatefield") = 'Sat','SATUREDAY',If(WeekDay("YourDatefield") = 'Sun','SUNDAY','')) as WEEKENDDAYS,)

and get sat and Sun day in new field,

you can use this  values in Chart or table expression without that huge script,

qlik performance would be slow by your script.

Regards,

K. Srinivasan.