topic Make a SUM() after a subquery with distinct in QlikView

Make a SUM() after a subquery with distinct

CarAlAg — Thu, 28 Apr 2022 18:46:34 GMT

Hello.

I had this up to now:

if(GetSelectedCount(n_year)=1,Round(sum({name_month} distinct cost_total),0.01),' ')

(where n_year, name_month are filters selected)

It worked pretty fine until I saw that cost_total can be repeated! so I need the field id_pr to separate them.

The solution in query is easy to see:

SELECT SUM(cost_total) FROM (SELECT DISTINCT id_pr, cost_total FROM facts_table WHERE... [filters i have in my dashboard])

Can anybody help me, please?

Re: Make a SUM() after a subquery with distinct

rwunderlich — Thu, 28 Apr 2022 21:04:31 GMT

The Qlik expression analog to a sub select is Aggr(). So something like:

Sum(Aggr(Only(cost_total)), id_pr))

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Make a SUM() after a subquery with distinct

CarAlAg — Thu, 12 May 2022 11:29:32 GMT

THX Rob!

In the end, I wasn't putting all the proper condition in the group by. You helped me focusing in solving the query I already had almost complete. I was in a point where I was trying some weird stuff