22 Replies Latest reply: Oct 22, 2017 7:03 AM by Sunny Talwar

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

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.

More precise question..

Want to calculate something like this where T1,T2...Tn are the values from 'Timekey' dimension selected by the user

((1+Sum({<Timekey={'T1'}>}Sales)) * (1+Sum({<Timekey={'T2'}>}Sales)) * ....) - 1

How to achieve this ??

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

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.

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.

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.

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.

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.

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.

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.

Hi

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

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

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.

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.

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.

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

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

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.

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.

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.

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.

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

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

So in the attached example, is something not working because of the selections you make?

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

Hi Sayyam

You should use sunny's solution as it is more flexible to change and adaptable. If you go for my solution changes will make your life tougher.

stalwar1 really nice way to approach the requirement with  exp and log . I guess Sayaam was trying to ask about =Exp(Sum(Aggr(Log(1+Sum({<TimeKey = {"\$(='>=' & Date(Min(Date#(Start_Date, 'YYYYMMDD'))) & '<=' & Date(Max(Date#(End_Date, 'YYYYMMDD'))))"}>}Sales)), TimeKey, ABC, DEF))) - 1

I think we can use  this instead if he thinks more of those value gonna come in future.

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

regards

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

I guess I am not really sure I understand this dynamic dimension from the sample app.. but if that's what the requirement is, I would use something like 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

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

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

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