DATA MODEL: How model data to analyze Quarter and Half Year from dates

[nboccassini]

Hi all,

I've a data like this (dates are in dd/mm/yyyy format) :

TYPE	PERIOD	INPUT
ID1	H12018	2
ID1	H22018	3
ID2	04/02/2018	1
ID1	Q22018	1
ID1	Y2018	5
...	...	...

I need a pivot to analyze different custom expression on Months, Quarters, Half and Year 

Data on Q,H and Y periods are cumulative, but the problem is the ID2 row when I want to analyze data on Q (for example). In a Count  expression, ID2 data must be counted if I want to see Months,Quarter etc.

How model data to analyze informations ?

Do you have suggestions?

[Oleg_Troyansky · Accepted solution]

Hi,

the main problem here is that the field PERIOD contains different formats of dates, years, quarters, etc... I'd recommend creating a Master Calendar table, which would associate the field PERIOD with a consistent set of date fields:

- The Date (the starting date of the period)

- Year

- Quarter

- Month

Once this table exists in the data model, and all PERIODs are properly described in the calendar table, then you can create analytics based on the Year, Quarter, and Month fields from the Calendar

Cheers,

[Brett_Bleess]

Couple of Design Blog posts in relation to Oleg's post that may be of further assistance:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Do not forget to close out your thread by using the Accept as Solution button if a post gets you the information you need, and if you do something different, you can post what you did and then use the button to mark that as the solution to let other Members know what you did.

Regards,
Brett