topic Re: Straight Table Calculation for the Previous Date Formula in QlikView

Straight Table Calculation for the Previous Date Formula

lylererger — Mon, 03 Jul 2017 14:12:30 GMT

Hi friends!

Help to write the formula that calculates values for the previous period inside current period in chart.

Like on screenshot below:

Trying to calculate measure "VALUE PREV DATE"

Sum(Aggr(sum({<Date={"$(=If(Dimensionality()=0,
Date(MonthEnd(AddMonths(max(Date),-1))),
Date(MonthEnd(AddMonths(max(Date),-1)))))"}>} [VALUE]),TB))

Doesnt works, because showing single value

Please help!

Thanks in advance.

Re: Straight Table Calculation for the Previous Date Formula

MK_QSL — Mon, 03 Jul 2017 14:18:21 GMT

What output you are looking for?

Re: Straight Table Calculation for the Previous Date Formula

lylererger — Mon, 03 Jul 2017 14:24:31 GMT

Like that:

TB	DATE	VALUE	PREV DATE	VALUE PREV DATE
1	01.01.2017	100
2	02.01.2017	200	01.01.2017	100
3	03.01.2017	300	02.01.2017
4	04.01.2017	400	03.01.2017
5	05.01.2017	500	04.01.2017

Re: Straight Table Calculation for the Previous Date Formula

MK_QSL — Mon, 03 Jul 2017 14:27:26 GMT

Only 100?

No value for other columns?

Re: Straight Table Calculation for the Previous Date Formula

lylererger — Mon, 03 Jul 2017 14:28:47 GMT

No It's not

Re: Straight Table Calculation for the Previous Date Formula

MK_QSL — Mon, 03 Jul 2017 14:29:42 GMT

I mean you want 100 for last column and 2nd Row only...
what about 3rd row, 4th row etc?

No value?

What is the logic behind the 100?

Re: Straight Table Calculation for the Previous Date Formula

lylererger — Mon, 03 Jul 2017 14:35:36 GMT

My falt. I need to get table like this:

TB	DATE	VALUE	PREV DATE	VALUE PREV DATE
1	01.01.2017	100	02.01.2017	200
2	02.01.2017	200	03.01.2017	300
3	03.01.2017	300	04.01.2017	400
4	04.01.2017	400	05.01.2017	500
5	05.01.2017	500

Re: Straight Table Calculation for the Previous Date Formula

lylererger — Mon, 03 Jul 2017 14:36:05 GMT

Any ideas ?

Re: Straight Table Calculation for the Previous Date Formula

MK_QSL — Mon, 03 Jul 2017 14:37:01 GMT

Is this Previous Date or Next Date?

Your Date Format is DD.MM.YYYY or MM.DD.YYYY?

Re: Straight Table Calculation for the Previous Date Formula

lylererger — Mon, 03 Jul 2017 14:47:35 GMT

Previous Date like Date(MonthEnd(AddMonths(max(Date),-1)))).

Date format DD.MM.YYYY

Re: Straight Table Calculation for the Previous Date Formula

MK_QSL — Mon, 03 Jul 2017 14:54:05 GMT

I am confused here.

TB	DATE	VALUE	PREV DATE	VALUE PREV DATE
1	01.01.2017	100	02.01.2017	200
2	02.01.2017	200	03.01.2017	300
3	03.01.2017	300	04.01.2017	400
4	04.01.2017	400	05.01.2017	500
5	05.01.2017	500

How come 02.01.2017 is previous date for 01.01.2017?

Re: Straight Table Calculation for the Previous Date Formula

lylererger — Mon, 03 Jul 2017 15:04:22 GMT

Omg. My falt...

FINALLY: I has field wiht dates ( only with first dates of months ).

01.01.2017

01.02.2017

01.03.2017

01.04.2017

01.05.2017

The table should looks like that:

TB	DATE	VALUE	PREV DATE	VALUE PREV MONTH
1	01.01.2017	100	01.12.2016	-
2	01.02.2017	200	01.01.2017	100
3	01.03.2017	300	01.02.2017	200
4	01.04.2017	400	01.03.2017	300
5	01.05.2017	500	01.04.2017	400

I need to calculate measure "VALUE PREV MONTH".