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: 
Not applicable

Using Variable Like A Regular Field

Hello,

Is it possible to use a variable in the same way that you'd use a field in an expression. I basically need to calculate a total in a text box that counts the number of records that have a specific variable value, but this can't be a field that I create in my script because it is based on the current selection - it can't be static. This is what I have in my text box expression and it's not working:

count(distinct if($(c_status) = -1, [Customer Number]))

Is there anything I could do to make this work?

Thanks!

16 Replies
sunny_talwar

Use the same set analysis in your expression for this particular chart and you should only see those customers which left

{<[Customer ID] = P({<[Production Date] = {"$(=Date(Min([Production Date])))"}>})-P({<[Production Date] = {"$(=Date(Max([Production Date])))"}>})>}

Not applicable
Author

Perfect, thank you!

Not applicable
Author

So I tried using these expressions in a straight table and I am running into an issue. The dimension I am using in the chart is 'Branch'  - it is possible for one 'Customer ID' to belong to more than one branch. When I have no selections made I get different counts using these expressions than when I select a specific branch. I am assuming that this has something to do with the use of distinct. For example id 'Customer ID' = 2 belongs to branch 23 and 25, so which branch will it be counted in on my straight table? Let me know if a sample document would make this easier.

Thanks

sunny_talwar

A sample would def. make it easier. Also, it would be helpful what do you desire to see in your table based on few different selections

swuehl
MVP
MVP

If the customer can belong to different branches, it will count towards each branch.

But the current set analysis might imply that the customer will not change branch between selected dates.

If you need to check existence of customer specific to a branch, it would be best to create a key in the load script made of branch and customer, and use this instead of customer id in your set expression

LOAD

     Branch,

     [Customer ID],

    AutonumberHash128(Branch, [Customer ID]) as BranchCustomerKey,

     ,,,,

Count( DISTINCT

{<[BranchCustomerKey] = P({<[Production Date] = {"$(=Date(Min([Production Date])))"}>})-P({<[Production Date] = {"$(=Date(Max([Production Date])))"}>})>}

BranchCustomerKey)

Not applicable
Author

This works, thank you. Will I need to make a specific key for every chart dimension that I choose to use with this expression? For example - if I'd like to use plan number as the dimension since it is possible for the 'Customer ID' to have switched plans over the span of selected dates.

swuehl
MVP
MVP

You probably need to create a key for any independent combination of dimensions. If you are coping with some kind of hierarchies, like customer relation to City, Country, Region, it might be sufficient to create the key for the most granular attribute combination.