topic Re: Trouble with an expression in QlikView

Trouble with an expression

Thu, 05 Jul 2012 14:50:36 GMT

Hello

Continuing my love hate relationship with QlikView I can't seem to work out something....

Basically I'm loading in a data like this

Site1, CCS1, dd/mm/yy

Site1, CCS2, dd/mm/yy

etc

then adding up all the scores to give me a total which is then multipled to give a financial value - hopefully you can see that on the attached example.

I've managed to work out a figure that gives me the cost of non-compliance (shown on table as Prediction minus Subsistence £13,132.35) but I can't seem to replicate this in a text box to make the figure stand out - how on earth can I copy this expression without showing all the individual site totals.

Thanks

Re: Trouble with an expression

swuehl — Thu, 05 Jul 2012 15:08:57 GMT

You should be able to do this using advanced aggregation, something like

=sum( aggr( YOUREXPRESSION, Site))

But, YOUREXPRESSION is really complex in your case, since you are referencing another expression in your column expression which is referencing another expression, which is referencing ....

You could try embedding all these expressions into one biiiig expression, or maybe try simplyfying your expressions (maybe by precalculating as much as possible in the script).

Hope this helps,

Stefan

Re: Trouble with an expression

Fri, 06 Jul 2012 15:36:36 GMT

Thanks, as I'm only a new to QV I think that is going to be too advanced for me at the moment, but I was thinking along the same lines which is promising.

Anyway to solve the problem, I've created a duplicate table, hidden all the columns except site name and cost, limited the number of rows to 3 and sorting show it shows the top 3 sites as well as the total cost of non-compliance.

I think this will work

Can I do a set analysis on this expression, so I can show the total regardless of the selection

Rank (TOTAL(cost))