Best Of
Re: trends ratio for last 4 months
I would try and sort on a single date field. If you don't already have it, and you just have the individual month and year columns, you can construct a date field like this in a formula tile.
DATE(CONCAT(`Month`,'/1/',`Year`))
Add this formula tile just before your rank and window tile and then sort on this date field descending. You can then add a row number or rank and then later filter to where it is 4 or less to get the most recent 4, if you need to limit it in your dataset.
Re: trends ratio for last 4 months
If you do have a date field, you can normalize a couple different ways to get them to be grouped by month and year. The easiest is probably using the Last_Day() function, like this (replacing dt with your fieldname that has the date:
LAST_DAY(`dt`)
This will create a date field for you and have all the transactions in a given month have the same date. You can then use this field in your rank and window and order by this field descending. Try that for starters before tackling the rank and see how it looks for you.
Re: Data Center Accounts Page Enhancements
Plus ability to select multiple accounts and do a bulk action.
Re: Calculated Field - Outstanding Invoice Count with date constraints
That formula won't validate as constructed. I would re-write it like this, assuming you have a due date field:
SUM(case when `type` IS NULL and DATEDIFF(`duedate`,CURRENT_DATE()) <=14 then 1 else 0 end)
This assumes type is actually null and not an empty string. I am using sum instead of count because we need to only count certain ones and we are using ones and zeros to do that. I'm assuming the due date is beyond the current date and so we are looking to see if it is within 14 days.
Hope this makes sense.
Re: Use power function in beast mode or in the formula while creating ETL
Hmm.... I just tried doing the same thing in the formula ETL tile and it worked. Here is what mine looks like using some sample data:
POWER((`DaysPast` / `Lag`),.2)-1
Do your rev Yr 5 and rev Yr 1 fields have nulls? It won't be able calculate unless they both have values. Is your formula validating in the editor, but then isn't producing results when you run the preview?
Re: Use power function in beast mode or in the formula while creating ETL
In your screenshot, it looks as though you are missing an open parentheses ( at the beginning as you should have two open parentheses together. You currently have one open parentheses and two closed ones.
Re: Hi, I'm getting an error doing DateDiff bw two date fields where Nulls exist in one data
Thank you Mark. Using the PeriodDiff did it for me. Thank you so much.
Re: Use power function in beast mode or in the formula while creating ETL
I have found that to misleading at times. If you move your cursor to the first parentheses after the sum, you should see which closed parentheses it is linked to. Due to order of operations, it is going to try and evaluate your revenue field divided by the sum of your other revenue field. I'm pretty sure that is not what you are intending based on what you described earlier.
Re: Use power function in beast mode or in the formula while creating ETL
It looks like you have a parentheses issue again. I see two open parantheses after your first sum. There should only be one there. You also have one too many, just before the comma. Try removing those and see if it works. Sometimes I will move things to separate lines to make it easier to read and make sure I have the correct number of parentheses.
POWER( ( SUM(`totalreported`) / SUM(`totalpaid`) ) ,.2 )-1
Re: Use power function in beast mode or in the formula while creating ETL
Yes, that will work in a card beast mode. Just keep a close eye on the parentheses. You had one too many in what you just sent.