Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Thanks in advance
Fabrizio
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
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
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)
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}
Hi All,
Many thanks for your help!
BR
Fabrizio