topic Re: date in set analysis in QlikView

date in set analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Qlikivew Team

I have table as below

TransDate	Server	TransAmt
1/15/2016	JAD	50
2/26/2016	JAD	60
3/2/2016	JAD	75
3/15/2016	JAD	86
1/15/2016	ZEO	25
2/26/2016	ZEO	65
3/2/2016	ZEO	58
3/15/2016	ZEO	85

i have to create pivot table as below and it has change appropriately when i make selection in TransDate filter . Please help me to create variable for max dt and previous dt and include with pivot table expression

Dimension Exp1 Exp2

Server Max(Transdate) Previous Dt

eg 3/15/2016 eg 3/2/2016

JAD 86 75

ZEO 85 58

Can you please guide me when we have use set analysis formula and when we have to use normal formula, please provide the some scanario and material for reference

regards

Ramu

Re: date in set analysis

Chanty4u — Wed, 16 Mar 2016 07:09:34 GMT

plz chk below

Dates in Set Analysis

Dealing with date formats in QlikView and QlikSense

A Primer on Set Analysis

Re: date in set analysis

avinashelite — Wed, 16 Mar 2016 07:10:04 GMT

try like this

1. Add Server as the dimension

2. Expression 1 = sum({<TransDate={"=max(TransDate)"}>}TransAmt)

2. Expression 2 = sum({<TransDate={"=max(TransDate,2)"}>}TransAmt)

Re: date in set analysis

Kushal_Chawda — Wed, 16 Mar 2016 07:13:31 GMT

Make sure that the Dates are in proper Date format. Then use below expression

Expression 1 = sum({<TransDate={"$(=date(max(TransDate)))"}>}TransAmt)

Expression 2 = sum({<TransDate={"$(=date(max(TransDate)-1))"}>}TransAmt)

Re: date in set analysis

tamilarasu — Wed, 16 Mar 2016 07:16:11 GMT

Use this expressions,

Sum({<TransDate ={"$(=Max(TransDate))"}>}TransAmt)

Sum({<TransDate ={"$(=Max(TransDate,2))"}>}TransAmt)

Re: date in set analysis

Wed, 16 Mar 2016 09:30:23 GMT

Hi,

Please try this.

Expression 1 = sum({<TransDate={"$(=max(TransDate))"}>}TransAmt)

Expression 2 = sum({<TransDate={"$((=max(TransDate)-1))"}>}TransAmt)

or make 2 variables

vmaxdate=max(TransDate)

vpreviousdate=max(TransDate)-1

Expression 1 = sum({<TransDate={$(vmaxdate)}>}TransAmt)

Expression 2 = sum({<TransDate={$(vpreviousdate)}>}TransAmt)