Waterfall Question

[#1]

Hello

I have a waterfall Pivot report.

In this report I have my Period Name column pivoted upwards.

Let us take an example

If I chose October-2013

It shows me all the future periods from October-2013 to lets say October-2018. That is about 60 months. There this having an impact on the performance of the report.

Therefore I want it to work like this

When I chose October 2013 it shows the periods only for that year so essentially it should show October-2013 November-2013 and Dec-2013 and rest of the periods it clubs them into years and shows years in this case therefore the desired output would be

Period Name  Oct-13 Nov-13 Dec-13 2014 2015 2016 Total

                        100      150       200   1000 2000  3000  6450

I have attached my excel one tab is how it is currently and the other tab is how I want it to be.

The expression that I am using to calculate the amount is below.

SUM(if([WF Period Start Date]>="$(=[Start Period])" and [WF RevPro Period End Date]<="$(=[End Period])" ,T_REVENUE+T_EREVENUE,0))

[rubenmarin]

Hi Rizwan, if Period Name is a dimension you can create as a calculated dimension, in example:

=Aggr(If(Year(Date)<=Max(Year(Date), Date(MonthStart(Date), 'MMM-YY'), Year(Date)), Date)

Most probably you'll need to add set analysis to ignore selections and allow the expression to create all the desired values. If you upload sample data I can give more help.

[#3]

Hello Ruben

Currently I have written my calculated dimension for period name as

following

*if(>="$(=[Start Period])" *

• and <="$(=[PP End Period])",[WF Period

Name],YEAR(Date#(,'MMM-YY')))*

where my start period variable is my period start date and the variable PP

End Period is the Year End Date.

But this expression just gives me years but not the desired result which is

if I chose Oct-2014 It should show me Oct-2014 Nov-201,Dec-2014 Period wise

and then the rest of the columns as 2015 2016 2017 and so on.

I have another idea which I will try but in the meanwhile any suggestions

are welcome.

Thx

Riz

On Thu, Apr 2, 2015 at 1:02 AM, Ruben Marin <qcwebmaster@qlikview.com>