topic Re: Calculation Variance within Script? in QlikView

Calculation Variance within Script?

sebastianfaust — Wed, 27 Nov 2019 14:35:58 GMT

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:
LOAD ID,
BalanceCode,
Value
FROM
input.xlsx
(ooxml, embedded labels, table is Balcode);

Concatenate
LOAD
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?

Re: Calculation Variance within Script?

Arthur_Fong — Thu, 28 Nov 2019 06:32:51 GMT

Try noconcatenate and drop the original Fact Table:

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

NoConcatenate

Table2:

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

drop table Table;

Re: Calculation Variance within Script?

qliksus — Thu, 28 Nov 2019 10:55:29 GMT

Just create inline for the dimension instead of loading the whole data to create it

LOAD * INLINE [
ID, BalanceCode, Value
A, ACT, 100
A, BUD, 70
C, ACT, 50
C, BUD, 40
];

LOAD * INLINE [
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) )

Re: Calculation Variance within Script?

Brett_Bleess — Tue, 10 Dec 2019 14:41:25 GMT

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

Re: Calculation Variance within Script?

hermanitor — Tue, 17 Dec 2019 08:33:18 GMT

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

Re: Calculation Variance within Script?

Brett_Bleess — Tue, 17 Dec 2019 16:02:22 GMT

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

Re: Calculation Variance within Script?

Kushal_Chawda — Tue, 17 Dec 2019 16:35:53 GMT

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))

Re: Calculation Variance within Script?

hermanitor — Fri, 20 Dec 2019 15:57:29 GMT

Hi,

Issue is resolved. Brett's soultion worked the best!
Thanks for your help.

Steve

Re: Calculation Variance within Script?

Brett_Bleess — Mon, 23 Dec 2019 14:09:52 GMT

Hey Steve, any chance you can let me know which solution worked, as I did not really give you guys anything! 🙂 It had to be Arthur or Qliksus, if you can let me know which one it was, I can mark that one as the solution, and we will have this closed out, unfortunately I did not make a note of which was selected before I unmarked it.... 😞 Thanks in advance and sorry for the confusion, I just want to be sure we get the right post marked that helped you guys.

Cheers,
Brett