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: 
antonaks
Creator II
Creator II

Variables from Excel

I ask for help in working with variables.

I load variables from an Excel file.
For example, vFact and vPlan. After I begin to conduct mathematical operations on them, the result behaves quite strangely.

For example, the expression =$(vFact) / $(vPlan) displays a huge number, although if you take the value of each variable separately, everything is fine.



1 Solution

Accepted Solutions
antonaks
Creator II
Creator II
Author

By trial and error, I came to this result.

I do not understand why, but if you write each variable in a new line, it works as it should.


// Not work

$(FactSales_CM_TD_Exec) / $(PlanSales_CM_TD_Exec)

// Good work

$(FactSales_CM_TD_Exec)

/

$(PlanSales_CM_TD_Exec)

// If use braces, they must be write in new line too

// Good work (if use braces)

(

$(FactSales_CM_TD_Exec)

/

$(FactChecks_CM_TD_Exec)

)

/

(

$(PlanSales_CM_TD_Exec)

/

$(PlanChecks_CM_TD_Exec)

)

View solution in original post

17 Replies
Anil_Babu_Samineni

Try to explain your input data of excel with variables? And what you are getting then what you needed?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
antonaks
Creator II
Creator II
Author

I'm using a download from a table with variables.

[Temp_Expressions]:

Replace only

LOAD

     ID_Expression,

     Name,

     Expression,

     Comment,

     Feature

FROM

(ooxml, embedded labels, table is EXPRESSIONS);

FOR i = 0 to NoOfRows('Temp_Expressions') - 1

    LET vName = Peek('Name', i, 'Temp_Expressions');

    LET vComment = Peek('Comment', i, 'Temp_Expressions');

    LET vFeature = Peek('Feature', i, 'Temp_Expressions');

    LET $(vName) = Peek('Expression', i, 'Temp_Expressions') & '// $(vFeature):' & ' $(vComment)' ;

NEXT i

antonaks
Creator II
Creator II
Author

Sample Source   

ID_ExpressionNameExpression
1CountTT_CM_TDCount(DISTINCT {$<CM_TD={1}>} [ID ФИЛИАЛА])
2FactSales_CM_TD_ExecSum({$<[НАЗВАНИЕ КАНАЛА]-={'Производитель'},[ID ТИПА ДОКУМЕНТА]={2,7,10}, [ВИД КОКУМЕНТА]={1,2,4,15,19,20,27,28,29,30}, CM_TD={1}>}([СУММА С НДС]*[ТИП ОПЕРАЦИИ]))
3FactChecks_CM_TD_ExecCount(DISTINCT{$<[НАЗВАНИЕ КАНАЛА]-={'Производитель'},[ID ТИПА ДОКУМЕНТА]={2,7,10}, [ВИД КОКУМЕНТА]={1,2,4,15,19,20,27,28,29,30}, CM_TD={1}>}DOC_FILIAL_KEY)
4FactQuantity_CM_TD_ExecSum({$<[НАЗВАНИЕ КАНАЛА]-={'Производитель'},[ID ТИПА ДОКУМЕНТА]={2,7,10}, [ВИД КОКУМЕНТА]={1,2,4,15,19,20,27,28,29,30}, CM_TD={1}>}([КОЛ-ВО]*[ТИП ОПЕРАЦИИ]))
Anil_Babu_Samineni

When you say =$(vFact) / $(vPlan) receive huge number. I am expecting try to split into 2 variables of 2 text boxes like 1) $(vFact)

2) $(vPlan)


Then let us know the values you are getting and then tell us the result you are expecting?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
antonaks
Creator II
Creator II
Author

Separately, each

$(vFact)  = 85 345 706

$(vPlan) = 85 377 077


Division

$(vFact)  / $(vPlan) = 85 342 240,17


Must be

$(vFact)  / $(vPlan) =0,9963 ...



biirka
Contributor II
Contributor II

Is your vFact formula of the form A - B?

If yes that would explain the issue you are having as you would get A - B / vPlan.

Try writing the formula as ($(vFact)) / ($(vPlan)).

Anil_Babu_Samineni

What are you getting when you use vFact / vPlan ??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
antonaks
Creator II
Creator II
Author

I wrote above:

$(vFact)  / $(vPlan) = 85 342 240,17

Anil_Babu_Samineni

I wrote above:

$(vFact)  / $(vPlan) = 85 342 240,17

But, My reply is

What are you getting when you use vFact / vPlan ??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)