Timestamp expression

[#1]

Hi All,

I have a problem with a Time Chart.

I have an database which contains data in the format below

Customer	Product	Year	Month	Turnover	Qty
A	Z	2015	1	400	1
B	Y	2016	4	200	2
C	X	2015	5	800	3
D	W	2015	8	500	4
E	Y	2016	6	600	5
F	W	2016	2	450	6

I would like to calculate (MTD and YTD)  TO and Qty.

Any suggestion about

• Base expression
• Timestamp expression
• Basedate expression

Thanks in advance

Fabrizio

[sunny_talwar · Accepted solution]

I guess the first step would be to create a date field (better than working with Year and Month fields separately). To create date, you can do like this:

Date(MakeDate(Year, Month, 1)) as Date

and then there are bunch of ways you can calculated YTD and MTD.

1) Create flags in the script - Creating A Master Calendar

2) Use set analysis to see a range of date - YTQ, QTD, MTD and WTD

3) Use As-Of Table - The As-Of Table

[#3]

Store current month & year into 2 variables.

V_mnth= Month(today())

V_year= Year(today())

for current YTD= sum({<Year=V_year>}Qty)

for current MTD= sum({<Month=V_mnth>}Qty)

[maxgro]

I think you need a date in your data to calculate YTD and MTD (periodo starting at the beginnng of current year / month and ending at the current date).

Then you can use the first link from Sunny post and set analysis, something like

sum({$ <CurYTDFlag={1}> Qty}

[#5]

Hi All,

Many thanks for your help!

BR

Fabrizio