topic Re: Date transformation in set analysis in QlikView

Date transformation in set analysis

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

hey all

if my data looks like this

id. Date. Balance

1. 1/30/2016 1000

2. 1/30/2016. 2000

1. 2/16/2016 1500

2 2/16/2016. 1300

my output i neet to show the balance of max date VS the last balance the previous month(last month has one date it can be any day during the month)

wut can be my expression hope u can help

Sample output:

id. Last month. Current month. Net evolution

1. 1000. 1500. 500

2. 2000. 1300. (700)

thank you

‌

sunny_talwar — Wed, 23 Mar 2016 09:37:18 GMT

May be this:

Dimension: id

Expressions:

1) Sum({<MonthYear = {"$(=MonthName(AddMonths(Max(MonthYear), -1)))"}>}Balance)

2) Sum({<MonthYear = {"$(=MonthName(AddMonths(Max(MonthYear), 0)))"}>}Balance)

3) =[Current Month] - [Last Month]

Kushal_Chawda — Wed, 23 Mar 2016 09:45:53 GMT

Create Straight Table. Make sure that your Date field is in proper Date format

Dimension - ID

Expression:

1)Last Month

=sum({<Date = {">=$(=monthstart(addmonths(max(Date),-1)))<=$(=monthend(addmonths(max(Date),-1)))"}>}Balance)

2)Current Month

=sum({<Date = {">=$(=monthstart(max(Date)))<=$(=max(Date))"}>}Balance)

3)[Current Month]-[Last Month]