Create a variable for previous month

[caccio88]

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‌

[sunny_talwar · Accepted solution]

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

];

[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]

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?

[caccio88]

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]

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