topic Re: Create a variable for previous month in QlikView

Create a variable for previous month

caccio88 — Mon, 06 Nov 2017 13:24:50 GMT

Hi guys,

that's my point. I'd like to create a table dipendent from two field of Date.

The first one is the month of the value aggregation and the second one is a condition.

I attach here what I'm looking for in excel example.

This is the final target table.

Jul-17	Aug-17	Sep-17	Oct-17
36	50	14	12

Thank you so much.

Filiberto

stalwar1‌

gwassenaar‌

Re: Create a variable for previous month

sunny_talwar — Mon, 06 Nov 2017 13:38:15 GMT

May be this

=Sum({<Flag = {1}>} Value)

Where Flag is created like this

Table:

LOAD *,

MonthName([Payment Date]) as MonthYear,

If([Due Date] <= MonthName([Payment Date]), 1, 0) as Flag;

LOAD * INLINE [

Due Date, Month DD, Payment Date, Month PD, Value

9/9/2017, 9.00 , 10/7/2017, 10.00 , 2

7/9/2017, 7.00 , 10/8/2017, 10.00 , 3

9/9/2017, 9.00 , 10/9/2017, 10.00 , 4

10/9/2017, 10.00 , 10/10/2017, 10.00 , 5

9/9/2017, 9.00 , 10/10/2017, 10.00 , 1

6/9/2017, 6.00 , 10/10/2017, 10.00 , 2

11/9/2017, 11.00 , 9/10/2017, 9.00 , 3

10/9/2017, 10.00 , 9/11/2017, 9.00 , 4

10/9/2017, 10.00 , 9/12/2017, 9.00 , 5

8/9/2017, 8.00 , 9/13/2017, 9.00 , 6

10/9/2017, 10.00 , 9/14/2017, 9.00 , 7

7/9/2017, 7.00 , 9/15/2017, 9.00 , 8

10/9/2017, 10.00 , 8/10/2017, 8.00 , 9

10/9/2017, 10.00 , 8/11/2017, 8.00 , 10

7/9/2017, 7.00 , 8/12/2017, 8.00 , 11

7/10/2017, 7.00 , 8/13/2017, 8.00 , 12

7/11/2017, 7.00 , 8/14/2017, 8.00 , 13

6/9/2017, 6.00 , 8/15/2017, 8.00 , 14

6/9/2017, 6.00 , 7/10/2017, 7.00 , 15

6/10/2017, 6.00 , 7/11/2017, 7.00 , 16

7/9/2017, 7.00 , 7/12/2017, 7.00 , 17

10/10/2017, 10.00 , 7/13/2017, 7.00 , 1

5/9/2017, 5.00 , 7/14/2017, 7.00 , 2

5/10/2017, 5.00 , 7/15/2017, 7.00 , 3

];

Re: Create a variable for previous month

sunny_talwar — Mon, 06 Nov 2017 13:42:12 GMT

Or this without creating the flag

Sum(If([Due Date] <= MonthYear, Value))

Re: Create a variable for previous month

sunny_talwar — Mon, 06 Nov 2017 14:45:29 GMT

May be better to do this as your flag (< instead of <=)

If([Due Date] < MonthName([Payment Date]), 1, 0) as Flag;

Re: Create a variable for previous month

caccio88 — Mon, 06 Nov 2017 18:41:48 GMT

Thank you Sunny stalwar1, as usual!

I think that my issue is a bit different from what I told you before...

I attach her my real issue.

What do you think about?

Thanks.

Fil

Re: Create a variable for previous month

sunny_talwar — Mon, 06 Nov 2017 19:12:20 GMT

Is this look like what you want?

Re: Create a variable for previous month

caccio88 — Mon, 06 Nov 2017 19:27:09 GMT

The output is what i'm looking for....

...but I was wondering if it could be possible to use as Date of the Mastercalendar "Due Date" and not "Payment Date".

Is it possible? stalwar1‌

Re: Create a variable for previous month

sunny_talwar — Mon, 06 Nov 2017 19:28:37 GMT

Would you be able to elaborate on why you want to do this?

Re: Create a variable for previous month

caccio88 — Mon, 06 Nov 2017 19:58:40 GMT

Yes, I'll try...

Cause in my real dataset i need do something different...

I attach here a new example to introduce you a new field "CLOSED/OPEN".

So I need to use the "Due Date" as my date for the mastercalendar.

Clear enough? stalwar1‌

Re: Create a variable for previous month

sunny_talwar — Mon, 06 Nov 2017 20:08:58 GMT

For this kind of requirement, I suggest creating a Canonical Date