topic Re: Need to reference a calculated dimension (aggr) in an expression in QlikView

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

sarahallen1 — Tue, 28 Jun 2016 14:59:42 GMT

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!

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

sunny_talwar — Tue, 28 Jun 2016 15:15:39 GMT

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

)

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

sarahallen1 — Tue, 28 Jun 2016 15:19:48 GMT

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

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

sunny_talwar — Tue, 28 Jun 2016 15:21:37 GMT

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

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

sarahallen1 — Tue, 28 Jun 2016 15:23:08 GMT

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.

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

sunny_talwar — Tue, 28 Jun 2016 15:27:06 GMT

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