call first date

[pgalvezt]

Hello,

I Have created a pivot table with many products, each products has different amount dates. I need to left always the first day product...

In the example (of course there are many products in the table) I have:

Products 038 and 043 with many dates. I would like to show just the older date.

So for 038 Product 06/06/2017

Product 043 05/07/2017.

Hope can help me with this,

Thanks!

[Anonymous]

Hello Pablo,

one of the options is that you calculate el first date for product in the script and create a flag for you identify what is the date

in the script:

left join(Temp_CceT_Movimientos)

load min(MovCceCalFecha1) as MovCceCalFecha1,

MovCceGlosa,

1 as _flagFirstDate

resident Temp_CceT_Movimientos

group by MovCceGlosa

in the layout

Sum({$<_flagFirstDate = {1}>}MovCceMontoLocalDebe - MovCceMontoLocalHaber)

[pgalvezt]

Hello Diego, This message appear in QlikView (ERROR). I think its take  a Min function as invalid expression...

Invalid expression

Left Join( Temp_CceT_Movimientos)

LOAD

CabCompId, 

     MovCceMontoLocalDebe,

     MovCceMontoLocalHaber,

     Saldo,

     MovCceGlosa,

     CtaId,

  min(MovCceCalFecha1) as MovCceCalFecha1,

     1 as _flagFirstDate,

Floor(Min(MovCceConFecha)) as MovCceCalFecha3,

     MovCceFecVenc,

     MovCceDigFec

Resident Temp_CceT_Movimientos

group by MovCceGlosa

[pgalvezt]

Anyone can you help me with this please?

Thanks!

[Anonymous]

Hi Pablo,

the problem is that the expression should have other additional fields

LOAD

CabCompId,

     MovCceMontoLocalDebe,

     MovCceMontoLocalHaber,

     Saldo,

     MovCceGlosa,

     CtaId,

  min(MovCceCalFecha1) as MovCceCalFecha1,

     1 as _flagFirstDate,

Floor(Min(MovCceConFecha)) as MovCceCalFecha3,

     MovCceFecVenc,

     MovCceDigFec

Resident Temp_CceT_Movimientos

group by

MovCceMontoLocalDebe,

     MovCceMontoLocalHaber,

     Saldo,

     MovCceGlosa,

     CtaId,

MovCceFecVenc,

     MovCceDigFec;

[pgalvezt]

Hello,

I attached the resut with the changes. Still giving me the same result...

My table has to show just 2 dates

For example if you do clic on c17/038 qlikview will show you 3 dates just I need the older and so on.... !

06/06/2017 c17/038 Range 121 days and

05/07/2017 c17/043... Range 91-120 Days

[maxgro]

this?

=aggr(Min(MovCceCalFecha1), MovCceGlosa_Homologacion)

[Anonymous]

ok, you don't can add all the fields, should add only the field call MovCceGlosa_Homologacion

[pgalvezt]

Hello Massimo,

Thank you for your reply,

The first table works perfect, but range still count 5 dates, can you help me with that?

Output must be:

06/06/2017 c17/038      Range 121 days and

05/07/2017 c17/043...    Range 91-120 Days

RANGE                          COUNT            Value

Range 121                          1                    12207

Range 91-120 Days            1                    15775

[maxgro · Accepted solution]

I added the range as an expression, result in image

=If('$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) <=30,'0-30 Días',

If('$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) >=31 and '$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) <=60,'31-60 Días',

If('$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) >=61 and '$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) <=90,'61-90 Días',

If('$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) >=91 and '$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) <=120,'91-120 Días',

If('$(=($(vFechaTerminoSys)))' - Min(MovCceCalFecha1) >=121, '121 Días')))))