Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
caccio88
Creator II
Creator 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
sunny_talwar

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

];

View solution in original post

9 Replies
sunny_talwar

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

];

sunny_talwar

Or this without creating the flag

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

sunny_talwar

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

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

caccio88
Creator II
Creator II
Author

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

sunny_talwar

Is this look like what you want?

Capture.PNG

caccio88
Creator II
Creator II
Author

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

sunny_talwar

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

caccio88
Creator II
Creator II
Author

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

sunny_talwar

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