topic Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense. in App Development

I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

Anonymous — Tue, 17 Oct 2017 20:48:51 GMT

How can i achieve multiply(sales) in qlik sense

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

Anonymous — Wed, 18 Oct 2017 12:04:28 GMT

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

m_s — Wed, 18 Oct 2017 12:17:52 GMT

Hello Sayyam,

maybe something like this will work?

(1+ Sum(Sales)) * (1+ Above(Sum(Sales))

Mathias

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

Anonymous — Fri, 20 Oct 2017 07:03:30 GMT

Thanks for replying Mathias , but how do i dynamically assign timekeys to my calculation.If you can look at sample qvf i have attached then you might be able to suggest some solution.

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

m_s — Fri, 20 Oct 2017 07:50:45 GMT

I guess I don't understand your issue because I took a look at your qvf and still think my formula is correct.

Why would you want to assign timekeys to your calculation? If the user selects one or more timekeys Qlik Sense will only calculate the sales of the current selection.

Mathias

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

Anonymous — Fri, 20 Oct 2017 08:26:11 GMT

Hi Mathias thanks again,

Sorry I am new to qlik so i might be confused, suppose i select 4 timekeys ,will this formula calculate 1+sum(Sales) seperately for each timekey and then multiply all those values ?

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

m_s — Fri, 20 Oct 2017 08:44:48 GMT

Yes it will. Suppose you have a table like:

TimeKey Sales

01/03/2017 100

01/03/2017 200

02/03/2017 300

03/03/2017 400

and an expression Sum(Sales) in a text object.

If you select 01/03/2017 the expression will result in 300.

If you select 01/03/2017 and 02/03/2017 the expression will result in 600.

If you select only 03/03/2017 the expression will result in 400.

I strongly recommend reading up on the basics of QlikView / Qlik Sense! (maybe try https://www.qlik.com/us/products/qlikview/getting-started)

Mathias

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

Anonymous — Fri, 20 Oct 2017 08:57:30 GMT

Hi Mathias

if i select 01/03/2017 and 02/03/2017 i dont want to simply add to get 600 i want (1+100+200) * (1+300)-1=90600

you get my point right,for 100+200 for 01/03/2017 and 300 for 02/03/2017 and then multiply the values.

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

m_s — Fri, 20 Oct 2017 10:33:49 GMT

I was just giving a simplified example of how the Qlik selection logic works (using the table i posted as data and the expression I posted as a measure) because you said you are not familiar with Qlik Sense.

Did you try the expression I posted earlier?

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

pradosh_thakur — Fri, 20 Oct 2017 11:22:07 GMT

check this out and test the data and tell me if it works.

hopefully this is what you want.

edit: sorry i forget to -1 . this is just the multiplication. you need to minus 1 to get the result

regards

Pradosh

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

sunny_talwar — Fri, 20 Oct 2017 11:22:41 GMT

Not sure, but may be this in a text box object

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

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

Anonymous — Sat, 21 Oct 2017 21:25:40 GMT

Hi Pradosh ,Thanks for the response

This works for me however i have an additional filter that i would like to apply here.

The user gets to select a start date and an end date from timekey dimension and i want this formula to work for that date range.

How to incorporate this date range filter in the formula you have suggested ??

Please let me know if you need further clarification.

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

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

you are going to use extension for start date end date from a single dimension?

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

Anonymous — Sat, 21 Oct 2017 21:47:03 GMT

No i cant use extensions

Start date and end date are seperate tables at all so selecting them doesnt change anything with calculations . What i am doing is storing the value selected from start date and end date in variables vStart and vEnd and now i want all the timekeys b/w vStart and vEnd to be automatically selected and applied to the formula you suggested. Something like this i want to add to set analysis

Timekey={'>= vStart <=vEnd'}

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

pradosh_thakur — Sat, 21 Oct 2017 21:50:47 GMT

That wont be a issue i suppose. You just have to use them by set analysis. Please post those variables details or the app like you posted before .

regards

Pradosh

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

Anonymous — Sat, 21 Oct 2017 22:22:05 GMT

I have Uploaded the updated qvf. All i want is whatever i choose from start and end date , automatically timekeys between start and end date should get selected and applied to the formula you suggested. Obviously user will select end date greater than or equal to start date. Thanks a lot for helping.

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

sunny_talwar — Sat, 21 Oct 2017 22:49:44 GMT

May be this

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

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

Anonymous — Sun, 22 Oct 2017 00:44:11 GMT

Do we have to hardcode ABC,DEF ? because i am choosing dimension at runtime.

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

sunny_talwar — Sun, 22 Oct 2017 00:47:55 GMT

‌At the runtime? What does that mean?

Re: I want to perform Multiply(Sales) instead of Sum(Sales). How can i do it in qlik sense.

Anonymous — Sun, 22 Oct 2017 01:07:18 GMT

i am choosing ABC or DEF from Select_Dim filter if you see the app again.