Problem getting the right Formula (total / aggr / set analysis)

Wed, 23 Jun 2010 14:59:45 GMT

Hi all,

the is something I can't get through. A have to get Table similay to this one:

I don't know how. I tried for 2 days I think... used total, set analysis and aggr, but I never get to my point. My example Excel-Sheet is not my source but my table I wan't to have in qlikview. In detail my Straight-Table is build like this:

Now, for PRICE_IN I need that AVG over the last 60 days PRICE_IN from source. For every dataset. Let's try an example. So PRICE_IN should not actualy by the PRICE IN set from source but should be calculated over 60 days.

DATE_OUT = 24.01.2010 WATER --> AVG should build between DATE_IN 22.01.2010 AND 22.01.2010 -60
DATE_OUT = 16.01.2010 WOOD --> AVG should build between DATE_IN 10.01.2010 AND 10.01.2010 -60

I could post 1000 of tries I did, if I didn't give up yesterday and came back to using PRICE_IN from source. For example how I break my head here one nice try...

=AVG(total <MATERIAL> IF(ISTEXT(MATERIAL),
IF(DATE_IN <= DATE_OUT,
IF((DATE_IN >= date(max({1} total aggr(date(max(IF(ISTEXT(MATERIAL),IF(DATE_IN <= DATE_OUT, DATE_IN-60)))),MATERIAL))),
PRICE_IN)))))

This one realy is horrible. Just one of really horrible tries...

Hope someone can understand and help.

Greets,
Jonas

AW:Problem getting the right Formula (total / aggr / set analysis)

Thu, 24 Jun 2010 09:19:16 GMT

Now I think it is impossible... Think there have to be sort of a vbs-macro or just something based in source-excel-file 😞 !

topic AW:Problem getting the right Formula (total / aggr / set analysis) in QlikView

Problem getting the right Formula (total / aggr / set analysis)

AW:Problem getting the right Formula (total / aggr / set analysis)