Dynamic Statistical Date

[katetsan]

Hi Expert,

Here's my question and if I explain not properly, please don't hesitate to indicate me.

I have Account Receivable data set, which has several fields. Two of them called "Accounting Date" and "Clearing Date".

I would like to create a bar chart, the x axis would be Statistical Year Month, which is not included in the AR data set.

And the value should be filter and accumulated by Statistical Year Month, for example,

If the statistical year month is 2019/3 , and the formula  should be "Accounting Date"<= 2019/3/31 and "Clearing Date">2019/3/31.

However, I've tried below expression in bar chart and it doesn't work well. Is there anyone could give me some instruction?

Expression:

sum({<[Accounting Date]={"<=Date"}, [Clearing Date]={">Date"}>}AR balance Amount)

Note:

I generate a "StatDate" table contain three fields which is Date, Year , Month for bar chart X axis (Statistical Year Month).

[JordyWegman]

Hi,

Try to create a variable with the date '2019/3/31'. Fill this variable in your formula:

sum({<[Accounting Date]={"<='$(VariableDate)'"}, [Clearing Date]={">'$(VariableDate)'"}>}AR balance Amount)

This is static, but you can see if your idea is working.

Jordy

Climber 

[katetsan]

Hi Jordy,

Thanks for your prompt reply.

What if the date '2019/3/31' is not just a static value, it could be several values as a Field?

Thanks.

[JordyWegman]

Could also be, but it depends in what kind of object you want to show your values. KPI or table maybe?

Did you get it to work?

Jordy

Climber

[katetsan]

Hi Jordy,

The object would be a bar chart or combo chart. I've tried to create a bar chart , dimension and expression is as below:

Dimension: Date (which is the statistical date start from 2017/1/31 to 2019/5/31)

Expression: sum( {< [Accounting Date]={"<='$(vStatDate)'"} >} AR balance Amount)

vStatDate=Date

It doesn't go well.

Kate

[JordyWegman]

Hi Kate,

Do you have some sample data that I can use? Then I'll give it a try.

Jordy

Climber

[katetsan]

Hi Jordy,

Here you are.

Thanks in advance.

Kate

[anushree1]

Hi,

Please check if the attached works i changed the variable to hold the date 15/03/2019 as 31/03/2019  and greater value doesnt appear in clearing date column

[JordyWegman]

Hi Kate,

Here I have an example. A good thing to do is to floor the dates. This makes comparing easier.

Jordy

Climber

[katetsan]

Hi Jordy,

It works. However, I would like to use dynamic date as x axis. 

I've create a date table in script and change the x axis of bar chart as attachment.

Thanks in advance

Kate