topic Using DISTINCT and SUM together in QlikView

Using DISTINCT and SUM together

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am a newbie here. I am facing issue, please suggest.

I have data like below, the data is not normalized so need to write some expressions.

I have Project data, quarter wise. As below figure states, I have repeated data for the same quarter.

I am trying to put expression is the chart, where the Approved budget will be the sum of the column where the quarters are distinct.

Please help as I am really struggling with the syntax.

Re: Using DISTINCT and SUM together

oknotsen — Sat, 06 Aug 2016 08:57:16 GMT

Moved from "Community Information" to "New to QlikView" as it is a technical topic.

@ Nishant:

If this topic was intended for Qlik Sense, let us know and we move it there instead.

Re: Using DISTINCT and SUM together

Sat, 06 Aug 2016 09:00:24 GMT

I am actualy new. to Qlikview. sO thanks for posting it to the right community

Re: Using DISTINCT and SUM together

MindaugasBacius — Sat, 06 Aug 2016 09:23:46 GMT

You could just simply do like this:

Budget:

Load Distinct

Project

,Financial_Year

,Quarter

,Approved_Budget

,Actual_Budget

From Source;

In this case you will only get data that contains Budgets, Years and Quarter. Everything will be done in your loading state, not front end.

If you wish something else please tell us more about your issue.

Re: Using DISTINCT and SUM together

Sat, 06 Aug 2016 09:30:47 GMT

Thanks for response!

I would need all the data in the excel, as it would be used in different tabs in Qlinkview to show different aspects.

In the current tab I need financial data. To represent Total project Buget, I would need to sum() the Amount, But only the amount where the Quarter column is distinct.

In other words, I need to pick only the rows DISTINCT Quarter, and then sum the Allocated_Budget amount.

Re: Using DISTINCT and SUM together

swuehl — Sat, 06 Aug 2016 09:42:51 GMT

I would agree that you should consider remodelling your data model so that your budget table does not show duplicate budget values.

If you can't do this, you can do this in the front end / chart expression like

=Sum( Aggr( Only(Approved_Budget), Project_Number, Financial_Year, Quarter))

Re: Using DISTINCT and SUM together

Sat, 06 Aug 2016 09:54:08 GMT

Thanks Stefan,

Even I wish to remodel, but there are some constraints which is not allowing me to

But your solution worked for me. Thanks alot.

Just to understand better, the above solution created a temporary table with (Project_Number, Financial_Year, Quarter) as dimension and took the Approved_Budget. can you tel me the role of ONLY, does it takes just the first row??

Re: Using DISTINCT and SUM together

Anil_Babu_Samineni — Sat, 06 Aug 2016 10:29:36 GMT

Can you please post sample example / Data?

Re: Using DISTINCT and SUM together

swuehl — Sat, 06 Aug 2016 11:05:43 GMT

No, it will return the one and only unambiguous value in the context of the evaluation, or NULL.

The Only Function

I've used the only() function to explictely express that I expect only a single unique value for budget in the context of project and year - quarter.

If your issue is resolved, then please close this thread by marking helpful / correct answers.

Qlik Community Tip: Marking Replies as Correct or Helpful

Re: Using DISTINCT and SUM together

Sat, 06 Aug 2016 18:41:46 GMT

Thanks Stefan.

Needed one more help. Suppose I want to showcase the Aproved budget vs Exenditure. what is the best suggested way to show that?

I am trying through line charts but doesn't look the way i expect

Re: Using DISTINCT and SUM together

swuehl — Sun, 07 Aug 2016 09:05:00 GMT

Try with a bar chart, a chart with dimension Year-Month and two expressions for Approved / Exenditure should show the data quite well.

Or what is the way you expect the data to look?