Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Using Variable Like A Regular Field

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

16 Replies
sunny_talwar
Not applicable

Re: Using Variable Like A Regular Field

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

Re: Using Variable Like A Regular Field

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

Not applicable

Re: Using Variable Like A Regular Field

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

Re: Using Variable Like A Regular Field

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

Not applicable

Re: Using Variable Like A Regular Field

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

sunny_talwar
Not applicable

Re: Using Variable Like A Regular Field

May be try this:

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

sunny_talwar
Not applicable

Re: Using Variable Like A Regular Field

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

Re: Using Variable Like A Regular Field

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

Re: Using Variable Like A Regular Field

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?