Level 1

0% to

Level 2

Tip /

to gain points, level up, and earn exciting badges like the new
Mission!

View all

Calculate (Sum) eVar values

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile
mahmoud_Alshala
Level 3

21-02-2018

Hi there

I have an eVar that captures account Balance for each member, I'm trying to find a way to get the sum of the eVar values per selected time period. For example

I need a calculated metric to give me the sum of the Account Balance eVar which will be for our example above (\$67)

Accepted Solutions (1)

hyderziaee
Employee

Likes

227 likes

Total Posts

465 posts

222 solutions
View profile

hyderziaee
Employee

Likes

227 likes

Total Posts

465 posts

222 solutions
View profile
hyderziaee
Employee

26-02-2018

You can try one more thing.

Create a numeric 2 classification on Account Balances.

Set the value as the evar value itself.

If you like to multiply the evar value by the number of instances, then set the rate and hinge to instance. Otherwise set them to fixed.

example: here is my sample bogus data.

 units Instances 87401 120 61438 111 310347 109 820673 100 83217 100 691723 100 38011 99 52388 96 843148 96

I created a numeric 2 classification, and sent in these values depending on the first column:

 Key ~numeric2 test ~numeric2 test^~id~ ~numeric2 test^~value~ ~numeric2 test^~period~ ~numeric2 test^~rate~ ~numeric2 test^~hinge~ 87401 87401 87401 2015/01/01 - 2035/12/31 instance instance 38011 38011 38011 2015/01/01 - 2035/12/31 instance instance 61438 61438 61438 2015/01/01 - 2035/12/31 instance instance 83217 83217 83217 2015/01/01 - 2035/12/31 instance instance 820673 820673 820673 2015/01/01 - 2035/12/31 instance instance 52388 52388 52388 2015/01/01 - 2035/12/31 instance instance 310347 310347 310347 2015/01/01 - 2035/12/31 instance instance 691723 691723 691723 2015/01/01 - 2035/12/31 instance instance 261438 261438 261438 2015/01/01 - 2035/12/31 instance instance 843148 843148 843148 2015/01/01 - 2035/12/31 instance instance 579872 579872 579872 2015/01/01 - 2035/12/31 instance instance

The date I have used is a large one which covers my historical timeframe and will work for the foreseeable future. You can keep what you like.

On uploading I can use the classification I have created as a metric:

As you can see it has multiplied the values with the instances. The total would be given at the bottom of the report. Those which are not classified (yet) are shown as zeroes. You can simply classify those values to correct them out.

Note that there is a limitation. Numeric 2 classification metrics are not available in Workspace or Ad Hoc

hyderziaee
Employee

Likes

227 likes

Total Posts

465 posts

222 solutions
View profile

hyderziaee
Employee

Likes

227 likes

Total Posts

465 posts

222 solutions
View profile
hyderziaee
Employee

22-02-2018

While you will not be able to do this Workspace, you can definitely achieve this in Report Builder. Excel will allow you to sum up the values that are returned by the report. You can have them multiplied by their instances as well.

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile
mahmoud_Alshala
Level 3

28-02-2018

Yes I did create calculated metric to try to trick Adobe by calling the metric from Workspace, but it didnt work

Thanks again for your help. Looking forward to seeing numeric 2 metrics in worksplace and ad-hoc analytics

hyderziaee
Employee

Likes

227 likes

Total Posts

465 posts

222 solutions
View profile

hyderziaee
Employee

Likes

227 likes

Total Posts

465 posts

222 solutions
View profile
hyderziaee
Employee

27-02-2018

Glad that this helped.

You are correct, you will need to import a value for it to work. You can do a browser export for a time frame (do 3 to 6 months at a time and classify those value which are not classified yet.

-----

~account balance \$ is an internal name. Not used anywhere except in classification. It is for your own identification. I chose to simply copy the key value over. Could be anything.

~account balance \$^~value~ is the actual numeric value to which the rate (here instances) is being multiplied to. Changing this would result in a different setup. Your scenario required the key column to be copied over.

-----

Also if you require decimal places, you can create a calculated metric and set the decimal places to two.

Note that this numeric 2 classification metric would only be available when you are running the parent report. For any other report, it will not show up for selection.

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile
mahmoud_Alshala
Level 3

27-02-2018

It worked!!!

Thank you so much hyderziaee

So I think the down side of this method is that you have to import all (historic) data manually, and any (future) value that was not mapped in the imported historic data it will not work, like value #10 below. is that correct?

Also would you please shed some light on why did we have to duplicate the values in these columns?

 Key ~account balance \$ ~account balance \$^~value~

 Key ~account balance \$ ~account balance \$^~value~ 0.00 0.00 0.00 3124.52 3124.52 3124.52

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile
mahmoud_Alshala
Level 3

26-02-2018

This would be awesome. I will give it a try and get back to you

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile
mahmoud_Alshala
Level 3

26-02-2018

hyderziaee thank you. I worked with report builder lightly as it's blocked internally in my company. The thing is, these values needs to be accessed by other users. are you aware of any other metric that can be used to sum \$ values other than revenue in purchase?

hyderziaee
Employee

Likes

227 likes

Total Posts

465 posts

222 solutions
View profile

hyderziaee
Employee

Likes

227 likes

Total Posts

465 posts

222 solutions
View profile
hyderziaee
Employee

26-02-2018

I dont see a way of doing this in Workspace.

Have you worked with Report Builder before? What you are trying to do can be done with it. It is basically an Add-in for Excel which pulls in the data to the excel sheet and you can apply custom formulae to get the results.

Let me know if you need more details.

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile
mahmoud_Alshala
Level 3

25-02-2018

I like your suggestion here. But again, how would you extract the eVar values so you can multiply it with the instances?

scott_albertsen
Level 1

Likes

0 likes

Total Posts

3 posts

1 solution
View profile

scott_albertsen
Level 1

Likes

0 likes

Total Posts

3 posts

1 solution
View profile
scott_albertsen
Level 1

23-02-2018

I am not exactly sure what it is your trying to calculate. The values in account balance is a bucket for the volume of accounts that have that balance (so you have 88 accounts with a 20 balance). If you wanted to sum the 88 accounts with a balance of 20 for a value of 1760, then I would create a calculated metric of multiplying the two variables.

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile

mahmoud_Alshala
Level 3

Likes

18 likes

Total Posts

41 posts

1 solution
View profile
mahmoud_Alshala
Level 3

23-02-2018

Thank you marion33678522 & hyderziaee

So if I want to approach this differently, and get the dollars count going forward similar to what we have in the revenue metric, without using the revenue metric as it's not actual revenue, what should I do? I mean, how can you report a value to an event instead of an eVar?

marion33678522
Level 7

Likes

167 likes

Total Posts

170 posts

5 solutions
View profile

marion33678522
Level 7

Likes

167 likes

Total Posts

170 posts

5 solutions
View profile
marion33678522
Level 7

22-02-2018

Hi !

I believe it's impossible to sum the values of an evar.

You should implement a counter event for those values, and then you can have the sum.

If not you can export the data via datawarehouse or report builder and do it yourself !

Cheers !

Kind regards,