topic Re: Date Analysis in QlikView

Date Analysis

akpofureenughwu — Wed, 25 Nov 2020 16:16:04 GMT

Good day everyone,

Normally when I do my date analysis.. my date comes in this format dd/mm/yyyy but I have a data set that has this format

Service Month	Invoice Number	Area	Invoice Value	VAT
01/01/2016	AA101	Area 1	1000	10
01/02/2016	AB201	Area 2	1400	14
01/03/2016	AW102	Area 2	3000	30
Excess and Excess Inv, 2016	AA121	Area 1	200	2
01/01/2017	AA110	Area 1	2300	23
Excess and Excess Inv, 2017	AE123	Area 2	1300	13

The Excess and Excess is more like financial activity that took place during the calendar year but the analyst can't pick the exact month (date). It's like an accrual concept.

Can you help me with the date creation?

Regards

Message was edited by: Akpofure Enughwure

Re: Date Analysis

Anil_Babu_Samineni — Thu, 07 Dec 2017 08:59:52 GMT

Good, Are you talking about transpose data. Will you explain the result set?

Re: Date Analysis

tomasz_tru — Thu, 07 Dec 2017 09:43:53 GMT

What output do you want in those problematic row? Maybe alt function?

ex.

alt( date#( ServiceM , 'DD/MM/YYY' ), [function for Excess and Excess Inv, ]

'NA' )

Re: Date Analysis

akpofureenughwu — Thu, 07 Dec 2017 09:50:59 GMT

I would love the month field to have this, Month :January, February, March, ......., Excess and Excess Inv

Year : 2016,2017

Thank you for the response

Re: Date Analysis

akpofureenughwu — Thu, 07 Dec 2017 09:52:09 GMT

I'm not transposing

I would love the month field to have this, Month :January, February, March, ......., Excess and Excess Inv

Thank you for r

Re: Date Analysis

johanlindell — Thu, 07 Dec 2017 09:52:54 GMT

LOAD If (Index ([Service Month], 'Excess and Excess Inv') > 0,

MakeDate (Trim(SubField ([Service Month], ',', 2))),

Date (Date# ([Service Month], 'MM/DD/YYYY'), '$(DateFormat)')

) as Date,

LOAD * Inline [

Service Month; Invoice Number; Area; Invoice Value;VAT

01/01/2017; AA110; Area 1; 2300; 23

Excess and Excess Inv, 2017; AE123; Area 2; 1300; 13

01/03/2016; AW102; Area 2; 3000; 30

01/02/2016; AB201; Area 2; 1400; 14

01/01/2016; AA101; Area 1; 1000; 10

Excess and Excess Inv, 2016; AA121; Area 1; 200; 2

] (delimiter is ';');

Re: Date Analysis

johanlindell — Thu, 07 Dec 2017 19:15:51 GMT

Date (Date# (Month, 'YYYYMM'), 'MMMM') as MonthName

should do the trick!