Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

caccio88
Contributor II

Create a variable for previous month

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-17Aug-17Sep-17Oct-17
36501412


Thank you so much.

Filiberto

stalwar1

gwassenaar

1 Solution

Accepted Solutions

Re: Create a variable for previous month

May be this

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


Capture.PNG

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

];

9 Replies

Re: Create a variable for previous month

May be this

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


Capture.PNG

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

Or this without creating the flag

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

Re: Create a variable for previous month

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

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

caccio88
Contributor II

Re: Create a variable for previous month

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

Is this look like what you want?

Capture.PNG

caccio88
Contributor II

Re: Create a variable for previous month

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

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

Highlighted
caccio88
Contributor II

Re: Create a variable for previous month

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

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