Making a Custom Date Calculation?

[Former Member]

Hello,

I've been looking around trying to find an explanation on how to do this, but I'm struggling to find anything of use.

I need to make a Custom Calculation to filter out any data older than 3 months. Basically, I just want my visualization to show data from the past 3 months from the current date. It needs to update when looking at the file to always only show the last 3 months of data (which is why I can't just use a filter).

Could anyone give me some insight on how this works?

Thanks,

Alec

[Former Member · Accepted solution]

What I've done in the past is create a custom dimension.  The basic formula looks something like this:

if (date today - date of the record) >= target number of timeperiods then 0 else 1

then filter for only the 1's (or 0's depending on what you need).

This will basically set a flag that allows for filtering.  Because you're using calculating relative to today (use the today function, not a date literal), the viz will dynamically update to only show the relevant months.

You may also need to do a ton of data manipulations, depending on your source, to get from a text date to an actual date field that allows for calculations.

FYI - in using Lumira, you will end up setting a TON of flags.  It's the easiest way to do some forms of conditional logic.

[Former Member · Accepted solution]

Hi Jay,

Thanks for the advice! I think I'm on the right track, but I'm struggling to finalize this. I'm trying to use the 'DateDiffInMonths' function, but I'm not sure what values to put in the function. I tried using 'Month(CurrentDate())' and 'Month({Date})', but those return an integer rather than a date value.

Also, I'm not really sure what to put after the conditions. Right now I have >= 3 (to signify 3 months), but obviously that's just not going to work.

Here's what I have right now:

[Former Member · Accepted solution]

I think I'm on the right track with this here:

if DateDiffInMonths(Month(CurrentDate()),AddMonthToDate(CurrentDate(), -3)) >= 3 then 0 else 1

If my understanding is correct, the DateDiffInMonths function should calculate the difference between today's date and today's date minus 3 months. I'm still not quite sure what to put after the >= though.

[Former Member · Accepted solution]

Here is an example formula from one of my Lumira vizes:

if CurrentDate() > AddMonthToDate({DATA_MONTH}, 4) then "Show" else "Hide"

As for your formula, I believe what you want is:

If DateDiffInMonths({Date}, CurrentDate()) >= 3 then 0 else 1

You are trying to insert a month value from your date fields into the DateDiffInMonths formula but that is an unnecessary step.  The formula will take care of that for you.

[Former Member · Accepted solution]

Oh I see! That makes perfect sense!

Lumira is taking the formula now, but I'm getting a different error. It's telling me "WARNING: Date that the calculation depends on no longer exist"

I'm not sure why this is the case...

[Former Member · Accepted solution]

In the formula, substitute whatever your date field may be for {Date}.  Lumira is failing because it's looking for a dimension called Date but I based that field name on your first image example.  Apparently that's not a field in your dataset.

[Former Member · Accepted solution]

I'm actually still getting the same error despite using the correct field name now... weird.

if DateDiffInMonths({Date Issue Submitted KEY}, CurrentDate()) >= 3 then 0 else 1

[Former Member · Accepted solution]

Welcome to the fun world of Lumira de-bugging!

Are you sure that the {Date Issue Submitted KEY} is a date field?  Especially if your datasource is BW, what looks like a date is oftentimes not a date at all, even if it's a key field.  For example, I know in our instance of BW, many date fields, even the keys, are actually year/month pairs (like 201601 for example).  That's the only thing I can think of that may be screwing up your formula.  Lumira is expecting an actual date value in there.  (That's one reason why I mentioned above that I go through contortions to get to a true date field in many of my Lumira vizes).

[Former Member · Accepted solution]

Debugging in any software always turns into such an adventure, haha.

It looks like Lumira realizes that this field is a date, because it puts a little calendar symbol next to it. It does however also have a little question mark:

That field is in a format that looks like this: 7/24/2016

I've also tried making a date/time hierarchy out of that field, but I get the same error when trying to use that in the formula.

Edit: Just to confirm, I am in fact taking this data straight from the BW.

[Former Member · Accepted solution]

Just for grins, try the version from my example:

if CurrentDate() > AddMonthToDate({Date Issue Submitted KEY}, 3) then "Show" else "Hide"

I have a vague recollection of having issues with the datediff formulas in Lumira, which may have been why I went with the formula I did.

[Former Member · Accepted solution]

Same problem unfortunately. 😕

[Former Member · Accepted solution]

Sorry  Alec - I'm out of ideas.  Date fields in Lumira and BW are a mystery to me and more times than not, I end up just trying different variations until I get to what I want.

[Former Member · Accepted solution]

No worries,

Thank you very much for your help! We made some progress at least, and I have a better understanding of how this works now.

I'll keep messing around with it and see if I get it. If I figure it out I'll try to remember to post it here.

Thanks!

[Former Member]

Hi Alec,

don't know what you are trying to do exactly. To get the date 3 month ago you can use:

AddMonthToDate(CurrentDate(), - 3)

Don't know if this helps....

Andreas

[TammyPowlas]

What is your data source?  If your data source is a BEx query, it is best/easier to do back in the BEx query

[Former Member]

It is a BEx query, but it does not have any parameters for the date. I can only bring in the Date field which I've converted into a Lumira Date Dimension. It's theoretically possible for me to request changes to the BEx query, but it would take weeks to go through the approval process and I need to have this done in a couple days, preferably today.

[TammyPowlas]

Alec:

I recommend looking at document SAP Lumira Calculation Best Practices | SCN - there are some good tips on date calculations that may help you.