Aggr - Max - Date in Date

[#1]

Hey,

I have a big problem on some Expressions. I get some numbers here, but the are not right at all. I tried alot with aggr und total and {1}, but nothing worked.

How can I get something like this to work:

=Sum( {<date={"<$(=max({<date={"<=$(lastdate )"},profit-={}>} date))"}>} profitplus)
-Sum( {<date={"<$(=max({<date={"<=$(lastdate )"},profit-={}>} date))"}>} profitminus)

I want all records labled with date<lastdate und profit<>0. For now I think it is impossible 😞 . Perhaps Aggr would help but I can't get it working.

Here another try:
=Sum(If(date < Aggr(max(If(date<=$(lastdate) AND profit <> '',date),dimension),profitplus)
-Sum(If(date < Aggr(max(If(date<=$(lastdate) AND profit <> '',date),dimension),profitminus)

Greets,
Jonas

[boorgura]

Give this a try:

Sum({1} if(date <= '$(lastdate)' and profit <> 0, profitplus)) -
Sum({1} if(date <= '$(lastdate)' and profit <> 0, profitminus))

This expression is to ignore all selections.

I might be wrong (if I dint the requirement right!!!)

[#3]

I'm not sure if I understand the exact situation, but maybe this thread can help you:

http://community.qlik.com/forums/p/37661/148129.aspx#148129

Is lastdate the same for everything (like the last date in a year) or is it different by record (like a customer's last invoice date, which will be different for different customers)? If it changes, that thread will probably give you what you want. Otherwise, set analysis is probably better (and faster). I didn't really look at Rocky's suggestion but something along those lines is probably b est.

Again, I'm not exactly sure what you want without an example, but whenever I have something like sum(if(aggr( I've always had it as sum(aggr(if. That certainly doesn't mean that what you wrote it wrong, but maybe it's worth considering.

[#4]

lastdate is always the same but the date by provit <> 0 differs each record. thats my problem in this case. I'll try this solution.

Rockys did not the sulution.

provit <> 0 should change the selected max date, not the selection of all records in sum. the max-date of used records should by smaller lastdate and smaller the last date in record with provit <> 0. So if I set lastdate on 31.12.2008 and last record with provit <> 0 is on 06.05.2008 it should only use records up to this date, even if there is another provit <> 0 in 2009.

Selection of revords should be on "date < lastdate AND date < aggr(max({1<provit-={}>}date),group)"

Edit:

The other thread could not help me. 😞

This one gives me the right date for each of my Dimensions. But it doesn't work together with my other Expressions.
=max({1<date={"<=$(lastdate)"},provit-={}>} date)

I need something like this - but if do not work...
=Sum( {1<date={"<$(=max({1<date={"<=$(lastdate)"},provit-={}>} date))"}>} value)

[#5]

This one takes me closest to solution:
=Sum( If(date< Aggr(max({1} If(date<='$(lastdate)' AND ISNUM(provit),date)),group), value))

Problem here is, QV now just takes the first record found in positiv If-clause. Each following record also in right if-clause is not in Sum.

[#6]

This is my Formula to get the right date:
=Max(total <WG_NrUnrBez> IF(Anlagedatum <= '$(Enddatum)' AND ISNUM(Zugänge), Anlagedatum))

Is QlikView able to use this in an calculation as compare? Something like this:
=Sum( If(Anlagedatum < max(total <WG_NrUnrBez> IF(Anlagedatum <= '$(Enddatum)' AND ISNUM(Zugänge), Anlagedatum)),Zugänge ))

I am on this for days now 😞 . Nothing seems to work. Someone has an Idea for me?

[#7]

Hello Jonas,

did take a look at your problems today. But I wasn't able to catch exactly all the things you want to achieve. Are you able to post a little exam app to clearify some details?

Regards, Roland