Rolling Totals

[Shiva123]

@HELLO,

           Everyone i am trying to match the totals in a straight table . Actually the column which  i am having issue is a combination of a division of two values and multiplied to another value, so the problem is  totals where the end user wants it to be  a sum of rows instead of expression total. The totals keep on changing for sum of rows when i change from one dimension to another in cyclic group . I  have tried sum(aggr(sum..), dimensions)) .. this did int work .Can anyone help me out on this .

Thanks in advance .

[vishsaggi]

Can you share some sample data to look into? Are you using straight table chart or Pivot table?

[Anil_Babu_Samineni]

When you multiply with some value that means your condition is not receiving exact match. Can you show the image ?

[Shiva123]

straight table...

[Shiva123]

hey ,

               anil  the value to be multiplied is correct, but the division value changes .(division is combo of  amt/qty)

[Shiva123]

hey , i  have  attached the app, the last column(TEST)  in the second table is the one which is changing .. When i change from one dimension  to another  in cyclic group .. Please let me know  if you need any further Information.

[Shiva123]

i have attached the app, would  you mind having a look  at that .

[sunny_talwar · Accepted solution]

Try this

=sum(aggr((SUM

    ({<$(cP2Filter), $(cUnitProdFilter)>}  //Comp Qty P2

    AGGR({<$(cP2Filter), $(cUnitProdFilter)>}

IF(

$(cZeroCheck)=0,

0,

IF (

  $(cFabsNew)

>= 0.501,

0,

SUM({<$(cP2Filter), $(cUnitProdFilter)>}[BB_QTY])

)

        ),

      $(vComparisonType))

)

)

*

//

(SUM

( 

// P1 ASP

//Comp P1

        (

          if(Currency='ST Cost Reported USD',if(Currency_ISO_CD<>'USD',

              ((aggr(SUM({<$(cP2Filter),Currency={"ST Cost Reported USD"}>}[ST Cost Reported USD]),[Product ID],Currency_ISO_CD))

                        /

                            (aggr(sum({<$(cP2Filter),Currency={"ST Cost Reported Local"} >}[ST Cost Reported Local]),[Product ID],Currency_ISO_CD))),1)

        *

    AGGR( {<$(cUnitProdFilter)>}

  IF(

    $(cZeroCheck)=0,

    0,

    sum({<$(cUnitProdFilter) ,Currency={"ST Cost Reported Local"}>}[ST Cost Reported Local])

    ),

      $(vComparisonType),Currency_ISO_CD,[Product ID]

    )

    )

  )

)

)

/

//Comp Qty P1

sum

(

        AGGR({<$(cUnitProdFilter)>}

  IF(

  $(cZeroCheck)=0,

        0,

SUM({<$(cUnitProdFilter)>}[BB_QTY])

          ),

$(vComparisonType)

)

),[Product ID]))

[Shiva123]

U are amazing !!!!!stalwar1‌ i have been trying these expression to work , since weeks .. but your  way of explanation helped me .. Thank u a lot sunny !!!!  One word for you .. YOU  ARE THE MASTERPIECE OF QLIK !!!!!!!