Announcements
cancel
Showing results for
Did you mean:
Partner - Creator

## Calculation Variance within Script?

hi all,

i have the following table,:

 ID BalanceCode Value A ACT 100 A BUD 70 C ACT 50 C BUD 40

within in the chart i can simple add 3 formulas:

Actual:

sum({<BalanceCode=ACT>}Value)

Budget:

sum({<BalanceCode=BUD>}Value)

Variance:

sum({<BalanceCode=ACT>}Value) - sum({<BalanceCode=BUD>}Value)

now i want to to the same in the script.

therefore i did the following:

Table:
BalanceCode,
Value
FROM
input.xlsx
(ooxml, embedded labels, table is Balcode);

Concatenate
ID,
'dif' as BalanceCode,
if(BalanceCode='ACT', Value, Value*-1) as Value
Resident Table;

now i have one formula: sum(value) and of course i have to put in the balance code as dimension.

works like charm.

but, in the real world i started with 19milion rows, and after that i have 38milion rows... and the performance goes down.

any ideas?

1 Solution

Accepted Solutions

You can use valelist if you don't want to increase your data

Dimension:

Valuelist('Actual','Budget','Variance')

Expression:

pick(match(Valuelist('Actual','Budget','Variance'),'Actual','Budget','Variance'),

sum({<BalanceCode={'ACT'}>}Value),

sum({<BalanceCode={'BUD'}>}Value),

sum({<BalanceCode={'ACT'}>}Value)-sum({<BalanceCode={'BUD'}>}Value))

8 Replies
Partner - Specialist III

Try noconcatenate and drop the original Fact Table:

Table:
BalanceCode,
Value
FROM
input.xlsx
(ooxml, embedded labels, table is Balcode);

NoConcatenate

Table2:

ID,
'dif' as BalanceCode,
if(BalanceCode='ACT', Value, Value*-1) as Value
Resident Table;

drop table Table;

Specialist II

ID, BalanceCode, Value
A, ACT, 100
A, BUD, 70
C, ACT, 50
C, BUD, 40
];

BalanceCode, BalanceCode_F,
ACT,ACT
BUD,BUD
ACT,VAR
BUD,VAR

];

In the chart level try the below :

dimension :  BalanceCode_F

Expression:  SUM({<BalanceCode_F=-{'VAR'}>}Value) + ( SUM({<BalanceCode={'ACT'},BalanceCode_F={'VAR'}>}Value) - SUM({<BalanceCode={'BUD'},BalanceCode_F={'VAR'}>}Value) )

Former Employee

Did either of the responses help you get a working solution?  If so, do not forget to return to the thread and use the Accept as Solution button on the post(s) that helped you get things sorted, as this will give the poster(s) credit for the assistance and lets other Community Members know what worked in this use case.  If you are still working on things, leave an update post.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Contributor III

Hi Brett,

Good morning to you.  Sorry didn't realise that Sebastian had posted this query on the forum and he showed me yesterday.
Have tried to make what you have advised work, but getting issues with the data on loading.  It is summing to zero over the contents of the data...which I know is wrong.

Any suggestions?

Thanks

Steve

Former Employee

I do not believe I marked the post as the solution, but I have unmarked it, please leave an update with what you guys still need in order to move forward.  You may need to attach sample app at this point for someone to be able to help figure things out.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

You can use valelist if you don't want to increase your data

Dimension:

Valuelist('Actual','Budget','Variance')

Expression:

pick(match(Valuelist('Actual','Budget','Variance'),'Actual','Budget','Variance'),

sum({<BalanceCode={'ACT'}>}Value),

sum({<BalanceCode={'BUD'}>}Value),

sum({<BalanceCode={'ACT'}>}Value)-sum({<BalanceCode={'BUD'}>}Value))

Contributor III

Hi,

Issue is resolved.  Brett's soultion worked the best!