8 Replies Latest reply on Nov 5, 2017 10:38 PM by Zhouyi Zhang

# Last 30 days calculation

Hi all,

I want to create a view with 4 measures - "Daily Connected", "Daily Disconnected", "Monthly Connected", "Monthly Disconnected". the data will be relevant for "yesterday", I'm setting this by filter on "INDEX()" calculation=1

I only have 2 of them - the daily ones, and I want to create a calculation for the "Monthly" ones. I tried this calculation: WINDOW_SUM(SUM([Daily Connected Accounts]), -30, 0), which worked great in a cross-tab (where I have all the dates) - but not so great in the view with the filter for the last date...

So my question is - How can I show the sum for last 30 days - if I'm filtering out those dates in the view?

Thank you!!

• ###### 1. Re: Last 30 days calculation

Hi, Matan

What I will suggest is use LOD.

e.g. yesterday

{Fixed: date([your date feild]):sum(if [your date field] = dateadd('day',-1, today()), then [Daily Connected Accounts] end)}

and last 30 days

{Fixed: date([your date feild]):sum(if [your date field] >= dateadd('day',-30, today()), then [Daily Connected Accounts] end)}

Hope this could give you some idea

ZZ

• ###### 2. Re: Last 30 days calculation

Hi.

First of all - Thanks for the answer!

but when I try: {Fixed: date([Date]):sum(if [Date] >= DATEADD('day',-30, today()), then [Daily Connected Accounts] end)}

OR {Fixed: [Date] : WINDOW_SUM(SUM([Daily Connected Accounts]), -30, 0)}

I get the same error - "expected '}' following the expression, with the red mark under the colon.

• ###### 3. Re: Last 30 days calculation

Good morning -0 the syntax on fixed expression is incorrect in your 2 expressions below you have 2 colons in each - LOD should follow a form of {Fixed [date]  : sum(... Jim

Let me know if this helps

• ###### 4. Re: Last 30 days calculation

Thanks, it did help the calculation!

BUT - it still didn't help me solve my problem...

The calculation in Zhouyi Zhang answer gave me the same value as for a regular SUM([Daily Connected Accounts]), and what I tried (WINDOW_SUM) can't be inside FIXED calculation ("Level of detailed expressions cannot contain table calculations or the ATTR function)

So matbe you have another solution? :\

Thanks!

• ###### 5. Re: Last 30 days calculation

So did you try what ZZ sent but with the correct syntax

• ###### 6. Re: Last 30 days calculation

Yes - I got the same value as the regular SUM([Daily Connected Accounts]).

Because this is what the calculation does - it sums per date, if the date is from the last 30 days - it doesn't sums up the last 30 days (as WINDOW_SUM does).

As I said before - the WINDOW_SUM calculation problem is that it's filtered with the date filter... So I can't actually get the last 30 days sum.

Still looking for other solutions if you have ones...

Thanks!

• ###### 7. Re: Last 30 days calculation

Could you share the workbook ?

Bests

Veronica

• ###### 8. Re: Last 30 days calculation

Hi, Matan

Don't use window_sum() in this case, just create two calculation fields as I suggested and drag them to the view.

It will be very helpful if you could share a workbook with dummy data but reflect your issue.

ZZ