Need to reference a calculated dimension (aggr) in an expression

[sarahallen1]

I've attached an example QVW showing a problem I'm working on.  Please read the notes in the text boxes to see what my problem is and what I've tried.

I want a 'data availability check' type of thing, which is used by other expressions to give warnings.  I tried to put the check as an expression but it doesn't work as it needs to ignore one of the dimensions (my set analysis here doesn't work as expected with <FieldToIgnore=> because I think it's in the dimension).  But then when it's a calculated dimension instead with aggr, I can't refer to it in the expressions.

I know that putting the check field or something in the script would be recommended but my data model is a lot more complicated than what I've put in this example (sorry I can't share the real example for privacy reasons) and I'd really like to achieve it in the front end if possible.

Thanks in advance for your help!

[sunny_talwar · Accepted solution]

Are you trying to get this?

=if([Change in Units]=0,null(),

  // using 'suppress zeroes' to hide rows where there genuinely weren't any transactions

  // this if statement prevents the data check from giving a value which would stop the suppression

if( count(TOTAL <RegisterType, Holder> {$<Holder=,Date={'$(=Date_End)'}>}Quantity)

  * count(TOTAL <RegisterType, Holder> {$<Holder=,Date={'$(=Date_Start)'}>}Quantity)

  =0 // if either is zero this will return zero (i.e. if there is missing data at the start or end date)

  ,'missing data',

  'data available')

)

[sarahallen1]

Yes!   Thank you.  Was completely overlooking 'total' - very helpful.

[sunny_talwar]

Does this resolve all your issues or we still have issues left? The reason I ask is because I was not sure if there were two issues or 1 issue to begin with

[sarahallen1]

This resolves my issue, thanks.

I had a question of whether we could reference calculated dimensions (which I don't think we can), but I don't need to use calculated dimensions now as can do it all in expressions. 

[sunny_talwar]

Awesome

Please close this thread down by marking correct answer if you are satisfied.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny