Pivot table, calculated dimension, Set Analysis doesn't work but AGGR might? HELP!

[#1]

I'm really getting into this Qlikview stuff now, but that brings its own burden that you start to think everything is possible - But enough of my personal problems.

Essentially I have information about projects in one table and against those projects resources are allocated by date in another table. Each project also has its own limit value above which resources can still be allocated but the Qlikview application must report the limit (and no more) - Simple so far?

The limit however only applies to one type of resource and the other resource(s) are free to grow as large as they want (and should be reported as such).

Just when you thought that was it, in steps the Finance people saying that there needs to be an overall (not project related) limit to the aforementioned resource - This limit overrides the first limit - For this I've used an input variable as this figure has been known to change...

None of the above is the problem and I've managed to do these using AGGR and lots of IF's. My problem is with what I thought would be simplest part, totalling.... I need to add the total for the completely unlimited resource to the 'limited limited' resource figure as a grand total.

I've tried different combinations of this for what seems like days now and I thought Set Analysis could help but it just throws errors everywhere (I think this might be because I'm using it in a calculated dimension on a pivot table???)

Can anyone help as I've starting to dream about this now.... [:S]

Maria

[fernandotoledo]

Have you tried creating flags in the script?

When you run script it should create 0/1 flags for each condition applyable to each term in your calculation.

Then the calculations inside the charts became as easy as

"sum(Term1 * flagTotal) + sum(Term2 * flagTotal)"

"sum(Term1 * flagOverall) + sum(Term2 * flagOverall)"

In other words, when you are reading the table with the values you can analyze, line by line, if it should be part of an expressions calculation. The flag in this case should be 1. And you may create one ore more flags for each expression.

It´s a little confusing, but the important is to get the idea.

Regards

Fernando

[#3]

Fernando! Thanks for the quick reply.

I did think about doing something like that but my problem there is that the Finance boys want their input box to change the 'Annual Limit' and I don't want to have to keep reloading (the real data takes about 10 minutes to load as there's lots of it!)

[llauses243 · Accepted solution]

Hi Maria,

This is my offer, moreover atach .zip

Good luck

From Spain, Luis.

[#5]

It worked perfectly...

Gracias Luis!

Maria