Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

Date Analysis

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 MonthInvoice NumberAreaInvoice ValueVAT
01/01/2016AA101Area 1100010
01/02/2016AB201Area 2140014
01/03/2016AW102Area 2300030
Excess and Excess Inv, 2016AA121Area 12002
01/01/2017AA110Area 1230023
Excess and Excess Inv, 2017AE123Area 2130013

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?


Message was edited by: Akpofure Enughwure

6 Replies

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

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


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

'NA' )

Creator III
Creator III

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

Creator III
Creator III

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


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


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

should do the trick!