Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sebastianfaust
Partner - Creator
Partner - Creator

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
Kushal_Chawda

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
Arthur_Fong
Partner - Specialist III
Partner - Specialist III

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
Specialist II
Specialist II

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

Brett_Bleess
Former Employee
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.
hermanitor
Contributor III
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

Brett_Bleess
Former Employee
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.
Kushal_Chawda

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

hermanitor
Contributor III
Contributor III

Hi,

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

Steve

Brett_Bleess
Former Employee
Former Employee

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