Create a new field joining two dates

[Anonymous]

Hello

I have the following case:

I need a new field, for example: FECHA PDN in order to create a table wich must have the sum of the FECHA DIGITACIÓN and FECHA AUDITORIA as a result.  In this case, if we want to know the result of 22/12/2016 would be 5.700 (1.085+ 4.615).

Thank you very much for your comments and time.

P:D I have a personal edition license, so I can not open any .qvw you could send to me.

[sunny_talwar]

This is what you need

Canonical Date

[Anonymous]

Sunny, thank you so much.

I will study that document you suggest. It seems to be a little hard to understand to me.

Best regards

[sunny_talwar]

I guess without a sample it was always going to be difficult

[vinieme12]

Are your date fields , fecha digitacion and fecha auditorium coming from different tables?

Are these tables associated?

Please post sample data that replicates your scenario

[Anonymous]

Thank you Vineeth

Both of them are from the same table.

I am using the following expressions:

To FECHA AUDITORIA:

count({<[ESTADO GENERAL]={'COMPLETADA'}, [PERIODO ACTUAL?]={'SI'}>} REGIONAL2)

To FECHA PROCESADAS:

count({< [ESTADO GENERAL]={'NO COMPLETADA'}, FASE={'INGRESADA A SW'}, [PERIODO ACTUAL?]={'SI'}>} REGIONAL2)

Attached a piece of the BD I am using

Thank you

[vinieme12 · Accepted solution]

using your sample

Dimension = DateIS

Expression = sum({<DateType = {'AUDIT','DIGI'}>}Processed)

/////////////////////////////// SCRIPT //////////////////////////////////////////////////////////////////////////////

FACT:
LOAD NUMERORADICADO,
REGIONAL,
[FECHA RADICACION],
[ESTADO CM],
[FECHA AUDITORIA],
[FECHA INGRESO RADICACION],
[FECHA DIGITACION],
FASE,
[ESTADO GENERAL],
[PERIODO ACTUAL?],
LOTE,
CUMPLIMIENTO,
[PRESENTA PRECINTO?],
rand()*10 as Processed,
rand()*9 as completed
FROM

(ooxml, embedded labels, table is [MAESTRA FACTURAS]);

MasterCalendar:
LOAD NUMERORADICADO,
[FECHA AUDITORIA] as DateIS,
'AUDIT' as DateType,
Year([FECHA AUDITORIA]) as YearIS,
Month([FECHA AUDITORIA]) as MonthIS
Resident FACT;

Concatenate
LOAD NUMERORADICADO,
[FECHA DIGITACION] as DateIS,
'DIGI' as DateType,
Year([FECHA DIGITACION]) as YearIS,
Month([FECHA DIGITACION]) as MonthIS
Resident FACT; 

///////////////////////////////End of SCRIPT //////////////////////////////////////////////////////////////////////////////

Read these posts

Search Recipes | Qlikview Cookbook

Tutorial - Using Common Date Dimensions and Shared Calendars

[Anonymous]

Vineeth Pujari, thanks a lot.

It works perfectly.

very thankful

[vinieme12]

Glad to help

Can you close this thread please , by marking a response as correct

Qlik Community Tip: Marking Replies as Correct or Helpful