- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')))))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Anyone can you help me with this please?
Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
this?
=aggr(Min(MovCceCalFecha1), MovCceGlosa_Homologacion)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ok, you don't can add all the fields, should add only the field call MovCceGlosa_Homologacion
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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')))))