topic sum for if expression in App Development

sum for if expression

rathorep — Tue, 05 Jan 2021 05:31:35 GMT

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.

Re: sum for if expression

tresB — Tue, 05 Jan 2021 05:40:09 GMT

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 (...

Re: sum for if expression

rathorep — Tue, 05 Jan 2021 06:31:36 GMT

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

Re: sum for if expression

tresB — Tue, 05 Jan 2021 06:37:41 GMT

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]))

Re: sum for if expression

ArnadoSandoval — Tue, 05 Jan 2021 07:26:44 GMT

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

Re: sum for if expression

rathorep — Tue, 05 Jan 2021 09:34:37 GMT

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 😞

Re: sum for if expression

rathorep — Tue, 05 Jan 2021 09:37:18 GMT

Hi @tresB

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 ...

Re: sum for if expression

ArnadoSandoval — Tue, 05 Jan 2021 23:59:34 GMT

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.

Re: sum for if expression

Ksrinivasan — Thu, 07 Jan 2021 08:24:14 GMT

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.