Using DISTINCT and SUM together

[#1]

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.

[oknotsen]

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.

[#3]

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

[MindaugasBacius]

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.

[#5]

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.

[swuehl]

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

[#7]

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

[Anil_Babu_Samineni]

Can you please post sample example / Data?

[swuehl]

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

[#10]

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