Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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())), '-')
Is this the chart you are talking about?
What is the issue here? What do you expect to see?
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)
Hi sunny,
i want to show data as below
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.
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()))
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())), '-')
Many Thanks Ruben its working for me.
Hi Ruben,
need one more help on the same
can i show the data from 2017Q3 like below
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.