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!

1 Solution

Accepted Solutions
sunny_talwar

Customers left

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

New Customers

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

Retained Customers

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

View solution in original post

16 Replies
sunny_talwar

Would you be able to share a sample app where we can see what exactly are you trying to do? Difficult to understand by looking at just the description

swuehl
MVP
MVP

At least post the definition of your variable c_status, and if possible some sample lines of records.

Not applicable
Author

I'm trying to get the text box called "Customers Lost" to work.

I need to be able to select any 2 Production Dates in my actual document and have this change based on that

sunny_talwar

You are nesting aggregations, you will most likely need to use Aggr() function.... What is the expected output from the sample shared?

Not applicable
Author

For this set of sample data there was one customer lost (the record with Customer ID = 1)

sunny_talwar

May be try this:

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

sunny_talwar

Customers left

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

New Customers

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

Retained Customers

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

swuehl
MVP
MVP

To help you answer your original question:

Variables won't hold something dimensional, like a table or field, they hold a single value.

You can use variables to be expanded to field names, though.

Learn more about variables, the importance of the context where they are evaluated in and what a 'dollar sign expansion' is in

The Magic of Variables

(and the blog post that are linked at the bottom).

Not applicable
Author

This works, thank you.

One more question - how would I create a chart that only includes customers left? Would I need to somehow get these expressions in a dimension and suppress when null?