topic Re: Sum of Distinct Issue In Qlikview in QlikView

Sum of Distinct Issue In Qlikview

mhmmd_srf — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am having an expression like Sum(DISTINCT SalesPlanned.).

Showing data against Month. Every Month level it is matching, but it is not matching in summary level. Similar Value we need to show in Single KPI.

If we actually calculate all the values of Month level it should come as 279413.6.

Can anyone please suggest.

Please help.

Thanks,

Sarif

Re: Sum of Distinct Issue In Qlikview

adamdavi3s — Thu, 09 Mar 2017 09:01:41 GMT

Why are you using sum distinct?

This will only sum distinct VALUES so if you have the same value in different months, this won't work.

To get the total this way you would need to use

sum(aggr(sum(DISTINCT SalesPlanned),CstFiscalYear))

Re: Sum of Distinct Issue In Qlikview

mhmmd_srf — Thu, 09 Mar 2017 09:14:52 GMT

Hi Adam,

I need to do this sum of distinct as we have two different Fact in different Granularity.

The above expression you mentioned is showing the same value.

But I need Overall sum as Actual Sum of all month level values.

Please help.

Thanks,

Sarif

Re: Sum of Distinct Issue In Qlikview

PrashantSangle — Thu, 09 Mar 2017 09:24:01 GMT

Hi,

What is your current expression ???

and what are dimensions do you have in your chart???

Regards,

Prashant

Re: Sum of Distinct Issue In Qlikview

mhmmd_srf — Thu, 09 Mar 2017 09:34:48 GMT

Hello Prashant Sir,

I need to show sum(DISTINCT SalesPlanned) in singlr KPI.

In list I am able to do it now. I got an option Total Mode as Sum of Rows. But how to make this in single KPI?

Please help.

Check the last column in List.

Thanks,

Sarif

Re: Sum of Distinct Issue In Qlikview

PrashantSangle — Thu, 09 Mar 2017 09:41:46 GMT

Hi,

What are difficulties you are facing when you are implementing Adms solutions.

If possible share your app??

Regards,

Prashant

Re: Sum of Distinct Issue In Qlikview

mhmmd_srf — Thu, 09 Mar 2017 09:48:50 GMT

Hello Prashant sir,

I am sharing my App.

My selection is Fiscal Year = 2017

Major Group = 006

and I need Text Object called Planned Sales should show 279413.6. Like last column in Table.

Please help.

Thanks,

Sarif

Re: Sum of Distinct Issue In Qlikview

adamdavi3s — Thu, 09 Mar 2017 09:52:14 GMT

Ok so my formula works, but I couldn't see your full field name 😉

=sum(aggr(sum(DISTINCT SalesPlanned),CstFiscalYearPeriodConcat))

Re: Sum of Distinct Issue In Qlikview

PrashantSangle — Thu, 09 Mar 2017 09:53:26 GMT

Hi,

I am getting below window.

Regards,

Prashant

Re: Sum of Distinct Issue In Qlikview

adamdavi3s — Thu, 09 Mar 2017 09:56:50 GMT

Yeah there are a whole bunch of filters on you need to take off.

My formula was correct but I couldn't see the full field name in the table and its sods law that the field

CstFiscalYear also exists!

Re: Sum of Distinct Issue In Qlikview

PrashantSangle — Thu, 09 Mar 2017 10:00:32 GMT

Yes you solutions is correct.

Regards,

Prashant

Re: Sum of Distinct Issue In Qlikview

hemachandran — Thu, 09 Mar 2017 10:11:11 GMT

HI try this expression,

=sum(aggr(sum(DISTINCT SalesPlanned),CstFiscalYearPeriodConcat))

Re: Sum of Distinct Issue In Qlikview

adamdavi3s — Thu, 09 Mar 2017 10:11:58 GMT

You will note that this solution has already been posted above.

Please try and read the whole thread before commenting

Re: Sum of Distinct Issue In Qlikview

mhmmd_srf — Thu, 09 Mar 2017 10:16:31 GMT

Yes Adam sir..you are correct.. looks like it is working..let me test it thoroughly..and will let u know..

Thanks a lot Adam sir.. for your kind help..

Thanks,

Sarif