null values

[himabinduch]

HI All,

i have data from 2014Q3to 2019 Q2 and i have one pivot table.

now i want to show data only form 2014 Q3 to 2018Q1 means need to show till current quarter  reaming should be null(2018Q2 to 2019Q2)  i have attched the sample data .

Thanks in Advance.

Thanks,

hima.

[sunny_talwar]

Is this the chart you are talking about?

What is the issue here? What do you expect to see?

[rubenmarin]

Hi Hima, I already see null() values for future quarters.

To hide those dimension values you can use a conditional dimension like:

=If(SHIPPED_DATE<=Today(), YearQuarter_SHIPPED)

[himabinduch]

Hi sunny,

i want to show data as below

[himabinduch]

Hi Ruben,

Thanks for your reply

but the above syntax will restrict the data as below . i need that one in Expression (mentioned another screen shot)

i want to display like below.

[rubenmarin]

There should be something with the data, "Max(TOTAL <YearQuarter_SHIPPED> CREATION_DATE)" returns 'Total Shipped %' for 2018Q1

With a mix of both it seems work but future quarters are hidden:

Conditional dimension: =If(SHIPPED_DATE<=Today(), YearQuarter_SHIPPED)

Expression: RangeSum(Before((count({$<SHIPPED_DATE={'*'},YearQuarter= >}DISTINCT ITEM))/(count(TOTAL <YearQuarter>{$<CREATION_DATE={'*'}>}DISTINCT ITEM)), 0, ColumnNo()))

[rubenmarin · Accepted solution]

This one is close, first quartes shows zero for some rows.

If(Min(TOTAL <YearQuarter_SHIPPED> SHIPPED_DATE)<=Today(), RangeSum(Before((count({$<SHIPPED_DATE={'*'},YearQuarter= >}DISTINCT ITEM))/(count(TOTAL <YearQuarter>{$<CREATION_DATE={'*'}>}DISTINCT ITEM)), 0, ColumnNo())), '-')

[himabinduch]

Many Thanks Ruben its working for me.

[himabinduch]

Hi Ruben,

need one more help on the same

can i show the data from 2017Q3 like below

[rubenmarin]

As each column needs its previous value, removing the initial columns most probably will change the result.

I'm not sure if this can be solved using an as-of table to access previous data:The As-Of Table

The first column expression will need access to the previous values, maybe with an aggr to do the accumulations bt step, some like:

If(Column()=1, Aggr(Expression, YearQuearter), Expression)

Another option can be having the accumulated values precalculated in script.