topic Re: Totals function - Auto and Sum in App Development

Totals function - Auto and Sum

Anonymous — Sat, 21 Oct 2017 19:26:37 GMT

When i use 'Auto' in total fuctions the total value i get is 5.35% but when i choose 'Sum' in Total functions i get 3.24%.

The individual values are same for both Value 0 and Value 1 as formula i am using here is

($(='(1+Sum({<B=Comp,DateField={"' & GetFieldSelections(DateField,'"}>} Sales*0.01)) * (1+Sum({<B=Comp, DateField={"') & '"}>} Sales*0.01))')-1)/100

My question :

i want to get total value using expression, so i put total just before sales in the formula

($(='(1+Sum({<B=Comp,DateField={"' & GetFieldSelections(DateField,'"}>} total Sales*0.01)) * (1+Sum({<B=Comp, DateField={"') & '"}>} total Sales*0.01))')-1)/100

and it gives me 5.35

How do i get 3.24 instead of 5.35 using expression ?

Re: Totals function - Auto and Sum

pradosh_thakur — Sat, 21 Oct 2017 20:20:02 GMT

This will shed some light perhaps.

https://community.qlik.com/thread/250400

regards

Pradosh

Re: Totals function - Auto and Sum

Anonymous — Sat, 21 Oct 2017 20:26:12 GMT

thanks for replying pradosh ,but that tells me the difference between auto and sum

my question is how do i achieve that sum total using expression because i need to use that total at some other place

Re: Totals function - Auto and Sum

sunny_talwar — Sat, 21 Oct 2017 20:32:05 GMT

May be this

Sum(Aggr(

($(='(1+Sum({<B=Comp,DateField={"' & GetFieldSelections(DateField,'"}>} Sales*0.01)) * (1+Sum({<B=Comp, DateField={"') & '"}>} Sales*0.01))')-1)/100

, YourDimensionsHere))

Re: Totals function - Auto and Sum

pradosh_thakur — Sat, 21 Oct 2017 20:37:36 GMT

what about

expression:

sum(($(='(1+Sum({<B=Comp,DateField={"' & GetFieldSelections(DateField,'"}>} total Sales*0.01)) * (1+Sum({<B=Comp, DateField={"') & '"}>} total Sales*0.01))')-1)/100)

Re: Totals function - Auto and Sum

Anonymous — Sat, 21 Oct 2017 20:46:29 GMT

Sorry,already tried this but it doesn't work because that formula you enclosed within sum gives a single value 5.35

Re: Totals function - Auto and Sum

pradosh_thakur — Sat, 21 Oct 2017 20:53:11 GMT

you tried sunny's expression? That shall work.

regards

Pradosh

Re: Totals function - Auto and Sum

Anonymous — Sat, 21 Oct 2017 21:09:12 GMT

with that i get 0.00%

Re: Totals function - Auto and Sum

sunny_talwar — Sat, 21 Oct 2017 21:09:56 GMT

Did you specify your dimensions in there?

Re: Totals function - Auto and Sum

sunny_talwar — Sat, 21 Oct 2017 21:10:41 GMT

Besides did you try the alternative approach to calculating what you intended?

=Exp(Sum(Aggr(Log(1+Sum(Sales)), TimeKey))) - 1

Re: Totals function - Auto and Sum

Anonymous — Sat, 21 Oct 2017 21:34:45 GMT

Thanks Sunny but it doesnt work, i tried this already

Re: Totals function - Auto and Sum

Anonymous — Sat, 21 Oct 2017 21:36:30 GMT

I tried GetFieldSelections(Dimension) there but doesnt work

Re: Totals function - Auto and Sum

sunny_talwar — Sat, 21 Oct 2017 22:29:16 GMT

GetFieldSelections(Dimension)? What is this? What is the dimension that you use in your chart?

Re: Totals function - Auto and Sum

sunny_talwar — Sat, 21 Oct 2017 22:31:51 GMT

I attached a sample, did you check that? Attaching again with an image

Re: Totals function - Auto and Sum

Anonymous — Sun, 22 Oct 2017 17:57:34 GMT

Hi Sunny Talwar,

This formula works

Sum(Aggr(

($(='(1+Sum({<B=Comp,DateField={"' & GetFieldSelections(DateField,'"}>} Sales*0.01)) * (1+Sum({<B=Comp, DateField={"') & '"}>} Sales*0.01))')-1)/100

, YourDimensionsHere))

but i am only able to add one dimension here. But if i use that pick formula here for dynamically choosing dimension it becomes 0.

Re: Totals function - Auto and Sum

sunny_talwar — Sun, 22 Oct 2017 22:18:03 GMT

There are too much back and forth between two threads of yours and I am confused what is your dimension? Have you tried this?

=Exp(Sum(Aggr(Log(1+Sum({<TimeKey = {"$(='>=' & Date(Min(Date#(Start_Date, 'YYYYMMDD'))) & '<=' & Date(Max(Date#(End_Date, 'YYYYMMDD'))))"}>}Sales)), TimeKey,

$(=If(GetSelectedCount(Select_1st_Dim) = 1,

Pick(Match(GetFieldSelections(Select_1st_Dim),'ABC', 'DEF'), 'ABC', 'DEF'),

'ABC')) ))) - 1