topic Re: Rolling Totals in QlikView

Rolling Totals

Shiva123 — Fri, 19 Jan 2018 23:05:08 GMT

@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 .

Re: Rolling Totals

vishsaggi — Sat, 20 Jan 2018 00:19:20 GMT

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

Re: Rolling Totals

Anil_Babu_Samineni — Sat, 20 Jan 2018 04:36:53 GMT

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

Re: Rolling Totals

Shiva123 — Mon, 22 Jan 2018 16:36:49 GMT

straight table...

Re: Rolling Totals

Shiva123 — Mon, 22 Jan 2018 22:06:25 GMT

hey ,

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

Re: Rolling Totals

Shiva123 — Thu, 01 Feb 2018 17:02:28 GMT

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.

Re: Rolling Totals

Shiva123 — Thu, 01 Feb 2018 17:03:45 GMT

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

Re: Rolling Totals

sunny_talwar — Thu, 01 Feb 2018 17:48:46 GMT

Try this

=sum(aggr((SUM

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

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

IF(

$(cZeroCheck)=0,

IF (

$(cFabsNew)

>= 0.501,

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,

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,

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

$(vComparisonType)

)

),[Product ID]))

Re: Rolling Totals

Shiva123 — Fri, 02 Feb 2018 00:01:08 GMT

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