Dimmension Aggr Problem

[pgalvezt]

Hi,

My Expression Works Fine:

=If(Isnull(Only({<fecha={'>=$(=Date(Max(fecha) - 31))<=$(=Date(Max(fecha) - 31))'}>} Field))='0',Only({<fecha={'>=$(=Date(Max(fecha) - 31))<=$(=Date(Max(fecha) - 31))'}>} Field),

'No Data').

When I try to do the thing in a dimmension calculated: Show some values null. Can you help me with this?

=Aggr(If(Isnull(Only({<fecha={'>=$(=Date(Max(fecha) - 31))<=$(=Date(Max(fecha) - 31))'}>} Field))='0',Only({<fecha={'>=$(=Date(Max(fecha) - 31))<=$(=Date(Max(fecha) - 31))'}>} Field),

'No Data'),Familia,Código)

Thanks!

[MarcoARaymundo]

Try this:

If(Isnull(Aggr(Only({<fecha={'>=$(=Date(Max(fecha) - 31))<=$(=Date(Max(fecha) - 31))'}>} Field),Familia,Código)),

     Aggr(Only({<fecha={'>=$(=Date(Max(fecha) - 31))<=$(=Date(Max(fecha) - 31))'}>} Field), Familia,Código),

     'No Data'

)

[evan_kurowski]

Pablo Gálvez wrote:

=Only({<fecha={'>=$(=Date(Max(fecha) - 31))<=$(=Date(Max(fecha) - 31))'}>}) Field)

Hello Pablo, I see you repeating this set-analysis a few times.  If I'm reading correctly, it seems like you're asking >= and <= to the same date (there is no range involved).

Could you get the same result from:  Only({<fecha={$(=Max(fecha)-31)}>} Field).  I don't think it would change your calculation, but it would shorten your expression.

Maybe what you want is a 31 day range:
=Only({<fecha={">=$(=Max(fecha)-31) <= $(=Max(fecha))"}>} Field)

You should also be able to skip wrapping the end-caps in Date() functions because set-analysis applied to a specific date field eventually needs to converts the calculation to a numeric comparison anyway.  If the Max() function returns a number, you don't need to add Date()

[pgalvezt]

Hi Evan,

The formula works fine for me because allows me visualize two dates. 14-08-2014 and 14-07-2014.

<=$(=Date(Max(fecha) - 31) Allows me visualize just 1 date 14-07-2014 that is fine.

The other fragment allows me visualize today. Until now the formula works fine. But I think that this rigid structure no allows me visulize other days. For example we were in March 31 -31 go to see another date in February and not specifically on February 28. You have any idea how to tell Qlikview dynamically manage dates? I would like clicking on March 31 show me 28 February. If I were in the fortnight of the month click on August 14 and July 14 show.

Thanks!

[evan_kurowski]

Hello Pablo,

You are on the right track in crafting an expression that calculates against a moving point in time based on end-user selection, vs. always calculating against one fixed point.

The good news is you already have it. 

Since I see you using the Only() function as the aggregation instead of something like Sum(), I'm assuming you may be doing some sort of snapshot structure instead of a cumulative model.

=$(=Date(Max(fecha))-31) will show you the value recorded 31 days prior to Max(fecha).  If max fecha without any selections applied is 14-08-2014, the expression will return the result for 14-07-2014.  Now try making a selection in the field [fecha] that is not 14-08-2014.  The expression Max(fecha) returns a different result and now the set-analysis should adjust accordingly.

If you select fecha = 31-03-2014, Max(fecha) = 31-03-2014, subtract 31 days = 28-02-2014.  Your results should be

Only({<fecha={28-02-2014}>} Field)

Be aware that you are using an exact distance of 31 days, this is not the same as saying the day in month... for example selecting 01-03-2014 (March 1st) should give you 29-01-2014. (January 29th).  If you expected March1st to equate to Feb1st you are trying to correlate the "Day In Month" sequence and that leads to more complicated handling because what do you do in scenarios where each month doesn't have the same amount of days?  It is possible in set-analysis for DayInMonth, DayInWeek, DayInQuarter, DayInCustomWorkDayCalendar, etc...   but these solutions are more complicated.

[pgalvezt]

Thank you for your reply, Im going to try to do the last thing (Dinamic dates) maybe in excel and after that replicate in Qlikview. Thank you for your advice.