multipliing by a formula "as a constant"

[Florian01]

Hello everyone, 

I have a complex formula which can be resumed as A*B with A being a formula using Sum and B using Aggr, Sum, Count,...

I want to show this formula over weeks and months using the accumulation option from the line graph and a master calender that I already implemented.

Through the graph accumulation the behavior of A*B is not correct.

B  is actually the calculation of a ratio so is to be used as a constant.

Is there a way to calculate B first and then to calculate A*B? In order to avoid the calucation of B to be impacted by the accumulation fucntion from the graph?

defining B in the Load editor is pretty complex because filed from multiple tables are used within B.

Thanks in advance

Best,

Florian

[marcus_sommer · Accepted solution]

If B is really a constant you could outsource the calculation into a variable.

Beside this I suggest to rethink the data-model because the recommended data-model is a star-scheme which means having a single fact-table with all measure-fields and n surrounding dimension-tables. Such approaches simplifies many things within the UI and probably also your calculation.

[Florian01]

Thanks for the answer Marcus! I read that variable only allow for a easier synax in formula but are basically calculated as if the whole formula where written so I assumed it would not help. Can you confirm this?

I never heard about the star scheme but it sounds interesting. I will look into it although I was hoping to find a "syntax based" solution 

[marcus_sommer]

A variable created without the leading = respectively with SET in the script contained only a string which could be used as formula within a calculation, like: v: sum(Field) and called per: $(v). But with the equal sign respectively LET in the script the content is evaluated before it's results is assigned to the variable.

[Florian01]

Thanks a lot it worked!