Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Specialist

## call first date

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!

1 Solution

Accepted Solutions
MVP

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')))))

9 Replies
Anonymous
Not applicable

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)

MovCceGlosa,

1 as _flagFirstDate

resident Temp_CceT_Movimientos

group by MovCceGlosa

in the layout

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

Specialist
Author

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)

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

Specialist
Author

Anyone can you help me with this please?

Thanks!

Anonymous
Not applicable

Hi Pablo,

the problem is that the expression should have other additional fields

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;

Specialist
Author

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

MVP

this?

=aggr(Min(MovCceCalFecha1), MovCceGlosa_Homologacion)

Anonymous
Not applicable

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

Specialist
Author

Hello Massimo,

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

MVP

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')))))

Community Browser