Create month field without taking informations from date fields

[#1]

Hi everyone,

I am creating a comparative analysis of various datas, and I am struggling with an issue I can't solve.

I work with projects, and each one has different status :

- projects received

- projects sent

- projects won

For each type of status, I created in the script date fields per month and year because for each project I have date fields "Reception date", "Sending date" and "Selling date".

Because of it I can analyse each status type separately.

But now I want to compare them amongst themselves and show in the same App how many projects I have received/sent/won per month.

And for that I can't use the date fields I already have, as they are specific to one status. If I put "Sending Month" as a dimension, I only see the datas related to projects sent, and not the others.

So I need to create a date field which won't be related to a status, which will be "neutral".

For exemple with a Project A :

- received on March 2015

- sent on June 2015

- won on September 2015

And a Project B :

- received on June 2015

- sent on September 2015

- won on November 2015

So I need to create a date field I can use as a dimension in a graph , and when I put my measures I will able to see received/sent/won all through the year. With my exemple :

- March 2015 : 1 received,

- June 2015 : 1 received, 1 sent

- September 2015 : 1 sent, 1 won

Etc.

I want to show everything.

How can I do that?

Thank you in advance for your help

[sinanozdemir]

I guess you can try to implement a master calendar, but the question is what date field the master calendar will be derived from. Do you think you can post a sample dataset?

[brunobertels · Accepted solution]

Hi Alexia

May be this blog post from hic will help you

https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

https://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

He explains how to create a mastercalendar with date fields from different table / project .

Regards

Bruno

PS : may be another approch would be to inner join your different project ?

[brunobertels]

Hi again

I just remembering this excellent post that helped me in the past :

https://community.qlik.com/blogs/qlikviewdesignblog/2015/02/27/qlik-sense-date-time

"we have introduced the Declare and Derive statements that make it easier for you to create a calendar definition that you can use for all date fields in your application."

Hope it will help you

Bruno

[#5]

Thank you!

The Canonical Date was exactly what I was looking for.

So now I have made my Canonical Month and Canonical Year, I am facing another problem in my result.

In my graph, I have a result problem (see in document attached) : on December I already have projects sent... When it is impossible.

My dimension is the Canonical Month.

My measures are the following :

Projects sent (= "Entrega") : Count({$<[Año Entrega]={$(=year(Today()))}>}Hijo)

Projects received (="Recepcion") : Count({$<[Año Recepcion]={$(=year(Today()))}>} Hijo)

So normally it should take for each one only the datas of 2015, right? Or am I doing a mistake in my measure?

[brunobertels]

Hi

in this mesure

Projects sent (= "Entrega") : Count({$<[Año Entrega]={$(=year(Today()))}>}Hijo)

For me you are counting the "Hiro" depending of this Year

Does hijo is the same than entraga ?

test in a text or KPI box

Bruno

[#7]

Hi,

"Hijo" is the project.

"Entrega" is "Hijos/projects sent" when "Recepcion" is "Hijos/projects received".

"Año Entrega" is the date field I created taking the Year of the sendind date,

"Año Recepcion" is the date field I created taking the Year of the reception date.

So in :

Count({$<[Año Entrega]={$(=year(Today()))}>}Hijo) : I want to count how many projects sent in 2015.

Count({$<[Año Recepcion]={$(=year(Today()))}>} Hijo) : I want to count how many projects received in 2015

And looking for why it appears datas on December for projects sent, it seems it is taking into account projects received on December 2014 and sent in 2015. There is like a weird mix-up occuring here, and I don't know how can I fix it.

[brunobertels]

Hi

first :

try to add a calculated dimension in your graph

year={$(=year(Today()))}>}

so that it take only values for current year.

Second :

change the formula for entraga with if statement to take only year 2015 or max year so that it will exclude project sent in 2014

third :

try here to put a conditional computation with year

( Never use it so i don't know how it works)

may be sent a sample of your APP so that community will help you

Regards

Bruno