topic Re: Summarizing Straight Table in QlikView

Summarizing Straight Table

Shiva123 — Thu, 07 Sep 2017 16:34:13 GMT

HI,

Everyone i have a typical scenario, i have a straight table which is composed of 8-10 dimensions and 15 expressions, it all works fine when i build as a straight table . The problem comes up when i roll it up meaning removing all the dimensions and adding them to cyclic group the totals on the expressions goes wrong .i even tried with pivot it didn't work. Is there any way to summarize this straight table .

Thanks in advance

Re: Summarizing Straight Table

sunny_talwar — Thu, 07 Sep 2017 16:38:18 GMT

Use Sum(Aggr(YourExpression, All8-10DimensionsSeparatedByComma))... For example

Sum(Aggr(Sum(Measure), Dim1, Dim2, Dim3, Dim4, ....., Dim10))

Re: Summarizing Straight Table

Anonymous — Thu, 07 Sep 2017 16:40:01 GMT

could be that you have a "problem" concerning the only() function and/or the dimension hierarchy.

Aggr() could be a solution for you. Please post some sample data...

Re: Summarizing Straight Table

Shiva123 — Thu, 07 Sep 2017 16:43:25 GMT

Hi,

sunny my expression is like this (sum(amount)*(1- sum(qty)) would the above solution possible in my expression

thanks in advance

Re: Summarizing Straight Table

rohitraut — Thu, 07 Sep 2017 16:53:31 GMT

Try this,

Sum(Aggr(sum(amount) Dim1, Dim2, Dim3, Dim4, ....., Dim10))*(1-Sum(Aggr(sum(qty), Dim1, Dim2, Dim3, Dim4, ....., Dim10)))

Re: Summarizing Straight Table

sunny_talwar — Thu, 07 Sep 2017 16:54:39 GMT

It would be this

Sum(Aggr((sum(amount)*(1- sum(qty)), Dim1, Dim2, Dim3,...., Dim10))

Re: Summarizing Straight Table

Shiva123 — Thu, 07 Sep 2017 18:21:36 GMT

Hi,

sunny what if i have a expression like this

ALT(IF ( FLAG = 'N', sum(Gross_AMOUNT), sum (Backlog_QTY) * round(avg(NET _ AMOUNT),.0001) ),0)

Thanks in advance

Re: Summarizing Straight Table

sunny_talwar — Thu, 07 Sep 2017 18:40:27 GMT

Same idea

Sum(Aggr(ALT(IF ( FLAG = 'N', sum(Gross_AMOUNT), sum (Backlog_QTY) * round(avg(NET _ AMOUNT),.0001) ),0), Dim1, Dim2, Dim3, Dim4, ....., Dim10))