topic Re: Creating date from year in QlikView

Creating date from year

Aspiring_Developer — Fri, 31 Jan 2020 07:06:11 GMT

Hi Team,

I am new to Qlik

I have the below data set :-

FY20	Country	WBS Element Description	PAR/WBS Element	Year	April	May	June	July	August	September	October	November	December	Januuary	Febuary	March
Capital	BR	Renaulat Wheels	907190-1	2020	10	20	30	30	30	30	30	30	30	30	30	30
Capital	BR	Spare Gear	907190-2	2020	100	200	300	400	500	600	700	800	900	1000	1100	1200
Capital	CZ	Replace Old Seat Covers	907190-3	2020	500	550	600	650	700	750	800	850	900	950	1000	1050

I have done the below in qlik script:-

CrossTable(Month, Amount, 6)
LOAD FY20,
Country,
'Actual' as Flag,
[WBS Element Description],
[PAR/WBS Element],
Year,
April,
May,
June,
July,
August,
September,
October,
November,
December,
Januuary,
Febuary,
March,
F18
FROM
[C:\Users\Vaishali\Documents\Work Varrooc\To be deleted Varroc.xlsx]
(ooxml, embedded labels, table is Data);

NoConcatenate

Temp:
Load *
,

Date(Floor(Year),'DD/MM/YYYY') as Date
Resident Data;
Drop table Data;

However, i am not able to able to created "Date" using Year field form the data.

I have tried :-

Date(Alt(Year, 'DD/MM/YYYY') as new date --> Didn't worked- getting output as ex:- 12/07/1905

Date(Date#(Year, 'YYYY'), 'DD/MM/YYYY') as New_Date-> Output : 01/01/2020 for all the months

Can any please help ?

Re: Creating date from year

tresB — Fri, 31 Jan 2020 07:20:05 GMT

Try like:

Date(MakeDate(Year, Date#(Month, 'MMMM')), 'DD/MM/YYYY') as Date

Re: Creating date from year

Aspiring_Developer — Fri, 31 Jan 2020 07:26:01 GMT

It did not worked.

It seems my moth is in text .

Not sure !

Any other solution

Re: Creating date from year

Brett_Bleess — Mon, 24 Feb 2020 21:26:54 GMT

Here is a Design Blog post to go along with what the poster was discussing:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Date-Function/ba-p/1463157

https://community.qlik.com/t5/Qlik-Design-Blog/Get-the-Dates-Right/ba-p/1476178

Have a look at those and see if they provide the missing info to get you going. The last thing would be Help content:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/DateAndTimeFunctions/date-time-functions.htm

That should cover most everything hopefully.

Regards,
Brett