How to get the count of previous period in Data Preparation

[qiaojinggg]

Hi, 

I'm trying to get the count of previous period (eg year) in data preparation which will gives me a new column of count.

An example of the output will be:

I'm really unsure of how to create the prev year column though. I managed to get this output through using Aggregated Measure > Relative Period function in Report Designer, however due to other requirements this method is not feasible for me. 

Hence, I need to somehow create a new column in data query to get the count of previous periods.

I tried to create a sub-query with a newly created column called Prev_year, which outputs me the correponding previous year (eg 2011 > 2010) and added the NUM_CNT inside the sub-query, however this didnt do the trick.

INTNX('year',  BP13_CANCEL_APPLICATION.DTE_CANCEL, -1, 'b')

Any idea how I can go about doing this? Any help will be greatly appreciated. Thank you 🙂

[RichardPaterson]

Hi, 

Is there a specific reason why you want to do this in the data builder? A simple aggregated measure will give you the same result. 

Richard

[qiaojinggg]

May i know how to do it in report designer? Using "RelativePeriod"?

I need to use the count of prev period to calculate percentage change. However, if i were to create it in report designer, it will be affected by the filter condition of the crosstab.

 

for my case, i only wish to display last 5 years of data on the crosstab (2011-2016). However, as you can see in the picture, the percentage difference for the "first year" (2011) is missing because data for 2010 is not displayed in the crosstab hence there's no reference to get the previous period count. 

I'm thinking if there's any ways to display the percentage difference for 2011 without displaying 2010 data. Hence, i wanted to explore if it's possible in data query. 

I'm open to any ideas or suggestions. 🙂