Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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])
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
At least post the definition of your variable c_status, and if possible some sample lines of records.
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
You are nesting aggregations, you will most likely need to use Aggr() function.... What is the expected output from the sample shared?
For this set of sample data there was one customer lost (the record with Customer ID = 1)
May be try this:
=Count(DISTINCT {<[Customer ID] = P({<[Production Date] = {"$(=Min([Production Date]))"}>})-P({<[Production Date] = {"$(=Max([Production Date]))"}>})>} [Customer ID])
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])
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
(and the blog post that are linked at the bottom).
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?