Question

Static value in dynamic value table

  • 26 November 2020
  • 3 replies
  • 113 views

Hi Everyone!

I’m looking for support on a quite simple calculation (I believe). I posted this in SQL already but think it lives best in data exploration. Many thanks in advance for your help!

I’m looking for a calculation of % to goal on a weekly basis.

Let’s say actual revenue per store = dynamic value in week 1, 2, 3, etc. 

This dynamic value should be compared with an average goal per week = static value, which is the same for every week throughout the year, but different from store to store. Each week should be compared to this. (I don’t know where and how this would be defined?)

Result of the calculation = % to goal = dynamic value / static value

 

In case the table is sorted not by store name column, static value associated to store should be moving with it of course.

 

weeks:         week 1                      % to goal              week 2.    % to goal   week 3   % to goal  ...

store 1.       dynamic value       static value

store 2

store 4

store 3

store 6

 

Thanks again for your quick help!

Sebastian


3 replies

Userlevel 5

What is it that you’re asking? Do you have your static value in your data as well? I think we need to see some explorations because right now you basically explained everything and I can’t see the issue? Is it about entering the static value? 

Thanks Dawid!
Yes I was looking for the formula to enter the static value in the table. Over the weekend I found a way and it’s working. 

if ( yes OR ${string}>0,
  if (${string}=("store1"),XX,
  if (${string}=("store2"),XX,
  null))
    ,null)

 

 

Userlevel 5

The only way to do it is to create if statement. Unfortunately, we don’t have an ability to create arrays within a look, sort of parameters but local to your explore.. 

Reply