topic Re: Timestamp expression in QlikView

Timestamp expression

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Timestamp expression

sunny_talwar — Tue, 09 Aug 2016 16:25:20 GMT

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

Re: Timestamp expression

Tue, 09 Aug 2016 20:55:25 GMT

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)

Re: Timestamp expression

maxgro — Tue, 09 Aug 2016 21:31:35 GMT

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}

Re: Timestamp expression

Wed, 10 Aug 2016 11:26:14 GMT

Hi All,

Many thanks for your help!

Fabrizio