Aggr in set analysis

[Fab93]

Hi all,

I have this SQL expression :

SELECT COUNT(id_fich_prjt) FROM 
(
SELECT id_fich_prjt,SUM(mt_prvs) AS dep_prev
FROM DTM_PROJET.dm_sicl_suivi_depenses_ae_cp
GROUP BY id_fich_prjt 
) as Toto
where dep_prev>=500000

Result = 388

I tried to transform this expression in Qlik sense with set analysis :

COUNT({< [mt_prvs] = {"=Aggr(SUM([mt_prvs]), [id_fich_prjt])>=500000"} >} 
[id_fich_prjt])

 Result = 2060

Where is the problem in my AGGR function ?

Thanks

[sergio0592]

Hello,

Try with :

=count(if(aggr(sum(mt_prvs),Id_fich_prjt)>500000,Id_fich_prjt))

[vinieme12 · Accepted solution]

Just this is sufficient

Count({<[id_fich_prjt]={"=sum([mt_prvs])>=5000"}>}[id_fich_prjt])

[Fab93]

Hi,

Thanks @sergio0592 and @vinieme12 . Both solutions work but the last one use set analysis.

Thanks again.

[vinieme12]

You can read more here

https://community.qlik.com/t5/Qlik-Design-Blog/The-Expression-Search/ba-p/1463907