Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

bwisealiahmad
Valued Contributor

Problem with Group By?

4.PNG

Hi,

I have a fact table and my goal is to do following calculation:

(23833,41 / 36) * 9

What I am having issues getting into the Fact Table is 36. 36 is a sum of the rows in "DagMatriseVerdi2018%".

I've tried to do a group by on %ProposalLine_%UkeID_Key and join back in but I get a really large number as you can see.

LEFT JOIN (Temp_Fakta_2)

Load

[%PropsalLine_%UkeID_Key],

Sum(DagMatriseVerdi2018) AS UkeMatriseVerdi2018,

    Sum(DagMatriseVerdi2018%) AS UkeMatriseVerdi2018%

   

Resident Temp_Fakta_2

Group By %PropsalLine_%UkeID_Key;

Any help is appreciated.

Best,

Ali

10 Replies
agigliotti
Honored Contributor II

Re: Problem with Group By?

i guess your question is not clear.

could you please post a sample app with mock data and show there your expected result?

pradosh_thakur
Valued Contributor III

Re: Problem with Group By?

does this help on front end

([your last column]/sum(total <%ProposalLine_%UkeID_Key>DagMatriseVerdi2018%)) * %ProposalLine_%UkeID_Key

MVP
MVP

Re: Problem with Group By?

Well - in the table you have a screenshot of this expression should do the sum you're asking for:

Sum(TOTAL [DagMatriseVerdi2018%])


Add an extra column and use the above expression just to verify that it calculates to 36 on all the rows.


If it does not return 36 on all rows it might be a "Sum of Expression" that does not calculate to the same of "Sum of Rows". Then the solution is often using the fantastic advanced aggregation function Aggr(...):


Sum( Aggr( Sum([DagMatriseVerdi2018%]) , %ProposalLine_%UkeID_Key ) )





bwisealiahmad
Valued Contributor

Re: Problem with Group By?

Test.PNG

There you can see in the correct column what I want it to be.

bwisealiahmad
Valued Contributor

Re: Problem with Group By?

Test.PNG

What it says in in the "Correct" column is what I am trying to create in the fact table on a date level.

MVP
MVP

Re: Problem with Group By?

Sorry - answering a bit quickly in the last response not noticing all the dimensions in the chart...

What does this calculate to in a measure in your chart?

Sum( TOTAL

     <%ProposalLine_%UkeID_Key,Uke,DagMatriseVerdi2018%,UkeMatriseVerdi2018%>

     [DagMatriseVerdi2018%]

)

bwisealiahmad
Valued Contributor

Re: Problem with Group By?

This also gives me 2268

MVP
MVP

Re: Problem with Group By?

I guess you have more than one year then?

MVP
MVP

Re: Problem with Group By?

Did you say 2268 and really meant that it gives 8568?