Visualisation

[Dann]

Hi!

i'm cuurently working with Qlik Sense on data visualisation (number of POL by month).

I represente the number of POL created by month, number of POL planned by month and number of POL closed by month in differente charts.

I need to represente number of POL created, planned, closed in the same chart  (line chart) with date ( month from 2017 to today ) as  x axis.

Can i generate this new dimension (month from 2017 to today)?

Find attached my POL_data,

Thanks

Dann

[OmarBenSalem · Accepted solution]

For that, you'll need to use a canonical date approach.

Here's a simple way of doing so using your script : 

Table :
load * Inline [
POL Code, POL Creation Date, POL Planned Date, POLClosure Date
PLOD21A0027 ,21/01/2021,25/03/2021,09/03/2021
...];

load "POL Code", "POL Creation Date" as Date,
'Creation' as FlagDate resident Table;

load "POL Code", "POL Planned Date" as Date,
'Planned' as FlagDate resident Table;

load "POL Code", "POLClosure Date" as Date,
'Closure' as FlagDate resident Table;

Now, you can use this new Date field as your dimension and the FlageDate as a second dimension with your count(code) as a mesure or use 3 measure and call your flagdate in a set expression :

somehting like

count({<FlagDate={'Closure'}>}code)

And for a better way, try to add a calendar to have all dates from 2017 and going !

If your data begins before 2017 and you wanna limit it to 2017, you can add to your measure somthing like :

count({<Date={"=year(Date)>=2017"}>}code)

et voilà ! 

[Dann]

Super OmarBenSalem!

Thank you for you answer,

Dann

[Dann]

Hi OmarBenSalem!

Do you have an idea of how i can add the line number of POL oingoing ( created and not closed) in the same Chart (number of POL created,  number of POL planned ,number of POL closed)  for each Date?

i tried set expressions like Count({<[Date]={">[Creation Date]"}*{"<[Closure Date]"}>}distinct[POL Code])...

Thank you for helping

Dann

[OmarBenSalem]

maybe somthing like :

count({<FlagDate=-{'Closure'}>*<FlagDate={'Created'}>}code)

[Dann]

Hi OmarBenSalem!

I tried it,  it seems to count just the  POL created in the same  week and not closed, it does'nt add the POL created the past weeks and unclosed .

is it possible to write something like this in Qlik Sense script?

*****************

Ongoing[]

For each D in Date

      a=0

      For each C in POL Code

          If Created_Date[C] <D<Closed_date[C]

                a=a+1

 Ongoing[D]=a

************************

Thanks