topic Re: Using Variable Like A Regular Field in QlikView

Using Variable Like A Regular Field

Thu, 16 Mar 2017 15:47:10 GMT

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!

Re: Using Variable Like A Regular Field

sunny_talwar — Thu, 16 Mar 2017 15:51:41 GMT

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

Re: Using Variable Like A Regular Field

swuehl — Thu, 16 Mar 2017 16:09:57 GMT

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

Re: Using Variable Like A Regular Field

Thu, 16 Mar 2017 16:15:50 GMT

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

Re: Using Variable Like A Regular Field

sunny_talwar — Thu, 16 Mar 2017 16:18:48 GMT

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

Re: Using Variable Like A Regular Field

Thu, 16 Mar 2017 16:20:22 GMT

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

Re: Using Variable Like A Regular Field

sunny_talwar — Thu, 16 Mar 2017 16:22:48 GMT

May be try this:

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

Re: Using Variable Like A Regular Field

sunny_talwar — Thu, 16 Mar 2017 16:26:32 GMT

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

Re: Using Variable Like A Regular Field

swuehl — Thu, 16 Mar 2017 16:33:50 GMT

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

Re: Using Variable Like A Regular Field

Thu, 16 Mar 2017 17:00:03 GMT

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?

Re: Using Variable Like A Regular Field

sunny_talwar — Thu, 16 Mar 2017 17:02:49 GMT

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

Re: Using Variable Like A Regular Field

Thu, 16 Mar 2017 17:07:51 GMT

Perfect, thank you!

Re: Using Variable Like A Regular Field

Fri, 17 Mar 2017 13:29:27 GMT

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

Re: Using Variable Like A Regular Field

sunny_talwar — Fri, 17 Mar 2017 13:44:18 GMT

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

Re: Using Variable Like A Regular Field

swuehl — Fri, 17 Mar 2017 13:54:20 GMT

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)

Re: Using Variable Like A Regular Field

Fri, 17 Mar 2017 14:55:55 GMT

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.

Re: Using Variable Like A Regular Field

swuehl — Fri, 17 Mar 2017 15:01:13 GMT

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.