Sum of Distinct Issue In Qlikview

[mhmmd_srf]

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

[adamdavi3s]

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

[mhmmd_srf]

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

[PrashantSangle]

Hi,

What is your current expression ???

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

Regards,

Prashant

[mhmmd_srf]

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

[PrashantSangle]

Hi,

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

If possible share your app??

Regards,

Prashant

[mhmmd_srf]

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

[adamdavi3s · Accepted solution]

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

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

[PrashantSangle]

Hi,

I am getting below window.

Regards,

Prashant

[adamdavi3s]

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!