6 Replies Latest reply on Nov 5, 2018 11:23 PM by raoul.tller

# Visualizing table calculations

Hi,

Currently I am in the process of doing some small what-if / adjustments to current data sets, we are now at the point where we have the result we want and now we want to visualize. Currently everything is in a text table and table calculations are applied, if I change the viz from text table to another it will not show the correct data anymore because of the calculations.

To give some context:

We currently have a balance every 30 minutes. We adjusted this balance to whats configured in the three parameters:

This works as we want, balance adjusted2 returns the correct results.

This is all on 30min interval but for the viz we want to change to Day and two lines (Balance versus Balance adjusted).

Currently I am using this LoD to return the latest balance (so the balance of 23:30):

IF {INCLUDE:MAX([Date])}=[Date] THEN [balance] END

The 23:30 balance is the only balance we need, so also for balance adjusted we just need this interval..

If I create a filter to only shows this interval the table calculation stops working.

DATEPART('hour',date) = 23 and DATEPART('minute',[Date]) = 30

TLDR

I want to visualize the Latest Balance (23:30 balance) versus Latest Adjusted Balance (23:30 Balance = 'balance adjusted2')

• ###### 1. Re: Visualizing table calculations

HI Raoul,

I am not sure what is your exact requirement, but here is one thing.

To get the last balance on a day, the formula should be something like this.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: Visualizing table calculations

Thanks, but can we do the same for balance adjusted2? If I use the same calculation I get this |(tried adding sum but that does not work either):

• ###### 3. Re: Visualizing table calculations

Your calc is already pretty much complicated and I am not sure this is exactly what you want anyways.

LOD does not work across two data sources, and I did with table calc.

If this works, please mark my answer as correct / helped t close the thread, not from inbox but from original post.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: Visualizing table calculations

Hi Shin,

Thanks again, definitely helps (and learning some tricks!).

What we are looking for is a basic line graph of Latest Balance SM versus Balance Adjusted (sm2) on Day of Date (but always showing the 23:30 or latest of both) which adjusts to the parameters.

• ###### 5. Re: Visualizing table calculations

With the data structure of blending, I don't know the way to "connect" the dots.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 6. Re: Visualizing table calculations

Interesting Shin, thanks a lot! Changed the mark type to bar for now and put DATE(date) on the axis and hid the others, will fiddle around with it a bit more today.

The next thing the end-user wants is the same but not the latest balance per day but the MIN balance (or lowest balance) per day. Currently I retrieve this value for Balance with this LoD:

MIN({INCLUDE [code],DATETRUNC('minute', [date]): SUM([balance])})

Probably will be able to work with window_min to get this result for both.