Refer to a field calculated in another table

[#1]

Hey everybody.

I am having a problem of this kind:

I have two sets of data:

- Historical data

- Current data

I organized the historical data in a pivot chart of 3 dimensions and a calculated field:

dimension 1 ---- dimension 2 ---- dimension 3 --- Expression 1 (calculated)

alpha ---- echo ----- charlie --- 0,5%

bravo ---- echo ----- bravo --- 0,3%

charlie ---- bravo ----- echo ---- 1%

I would like to use expression 1 with Current data, but Current data have a dimension more; my desired result would be something like:

dimension 1 ---- dimension 2 ---- dimension 3 --- dimension 4 ----Expression 1 (calculated)

alpha ---- echo ----- charlie --- echo ----0,5%

alpha ---- echo ----- charlie --- charlie ----0,5%

alpha ---- echo ----- charlie --- delta ----0,5%

bravo ---- echo ----- bravo --- delta ----0,3%

Is it possible or I have to go back to excel and use a vlookup? 🙂

Thanks a lot everybody in advance, I really appreciate your effort,

[#2]

Your question is not clear to me.

As per my understanding if there is linking between your current data & historical data then simply put another dimension in the table.

or if u want to keep historical data table & current data table separate then crate a flag for historical & current data & in the historical data table exprression call this flag for historical & in current data table exprression call this flag for current .

or you can attach one small example.

[#3]

Do you want to show the current and historical data in two different charts? If so, you can use the same expression in the second chart.

correct me if my understanding is wrong

[#4]

mmh I have not been clear, sorry.

think about Customer Performance.

In the first table (Historical) I calculate the average spending per month of different clusters of Customers and the output is:

Category ---- Region ---- Ageing ----- Avg Spending per month

Consumer---Europe----1-5yrs----15€

Business---US---------->10yrs-----50€

the calculation is made on the hisorical data of these Customers. The expression is something like avg(spending).

I want to apply this exact calculated value as a parameters for a simulation based on current Customers. In the simulation thouhg I want to add a dimension not included in the historical data, like

Category ---- Region ---- Ageing -----Hair Color---- Avg Spending per month

Consumer---Europe----1-5yrs---------Red-------------15€

Consumer---Europe----1-5yrs---------Brown-------------15€

...

In this second table, the field "Avg Spending per month" cannot be calculated on Current Data (I do not have the spending info) but I want to use as a paramters the value calculated in the historical table.

In other words I have to take from the other table the information that "The average spending of the consumer, european customers between 1 and 5 years age is of 15€, indipendently from the hair color...

not sure this clarifies... hope so!

[qliksus]

I am not sure i have understood u correctly but i think u want to calculate

"Avg Spending per month" based on Category ---- Region ---- Ageing

without considering Hair Color if this is the solution u wants

u can use the expression aggr("Avg Spending per month",Category , Region, Ageing )

[#6]

attaching a simple cause I am not able to explain myself 🙂

thanks all,

Lorenzo

[#7]

nobody? I am sadly leaning to Excel... 😞

[johnw]

If I understood the question:

aggr(nodistinct avg([Spending €]),Region,Product,Ageing)

[#9]

You can try this one too:

avg(TOTAL <Region,Product,Ageing> [Spending €])

[#10]

Your requirement is not very much clear.I would like to know why you have suppressed zero values for the dimension city.

Sloution1. If you uncheck suppress zero values for city & use the same expression avg([Spending €]) as the top table you will get the values. In this case u'll get all averages where citi will be blank because of the synthetic key u are using.

Solution2.I don't know whether it is possible or not but if possible then remove ID_Cli field from synthetic key & only use Region,Product & Aging.Then follow solution1 again.