Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

Calculation Variance within Script?

hi all,

i have the following table,:

IDBalanceCodeValue
AACT100
ABUD70
CACT50
CBUD40

 

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? 

 

1 Solution

Accepted Solutions

Re: Calculation Variance within Script?

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

View solution in original post

8 Replies
Partner
Partner

Re: Calculation Variance within Script?

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;

qliksus
Valued Contributor II

Re: Calculation Variance within Script?

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

Digital Support
Digital Support

Re: Calculation Variance within Script?

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 don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.
hermanitor
New Contributor

Re: Calculation Variance within Script?

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

Digital Support
Digital Support

Re: Calculation Variance within Script?

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 don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.

Re: Calculation Variance within Script?

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

View solution in original post

hermanitor
New Contributor

Re: Calculation Variance within Script?

Hi,

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

Steve

Digital Support
Digital Support

Re: Calculation Variance within Script?

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

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.