Set analysis with aggr fuction

[#1]

Hello there,

I am struggling to get this set analysis right to get the previous compare date value against the current selected business date.

Please, find the below sample data and will appreciate your help and quick response.

Metric ID	LBD	BD	COMPARE_BD	MetricValue
1	2014-08-23	2014-08-25	2014-08-20	100
2	2014-08-24	2014-08-25	2014-08-21	200
3	2014-08-25	2014-08-25	2014-08-22	300
1	2014-08-26	2014-08-28	2014-08-23	1000
2	2014-08-27	2014-08-28	2014-08-24	1500
3	2014-08-28	2014-08-28	2014-08-25	600

The out required as below for the selection BD = '2014-08-28'

Metric ID	Value	Compare BD	Previous Value
Total	3100	-	600
1	1000	2014-08-23	100
2	1500	2014-08-24	200
3	600	2014-08-25	300

I am using set analysis with aggr function but it's not giving me the right value as it is showing the current values only

Compare BD = only(aggr(NODISTINCT only(COMPARE_BD),[Metric ID])) - which is working fine

Sum({<"LBD = {only(aggr(NODISTINCT only(COMPARE_BD),[Metric ID]))}" >} MetricValue)

Please, let me know if you required more information on requirement.

Thanks,

S

[jagan]

Hi,

Try like this in script

Data:

LOAD

*,

If(Previous(MetricID) = MetricID, Previous(MetricValue)) AS PreviousValue;

LOAD

*

FROM DataSource

ORDER BY MetricID, COMPARE_BD;

Now use PreviousValue in your set analysis.

Regards,

Jagan.

[#3]

Hi Jagan,

Thanks for your reply, appreciate it.

This is just sample data I provided here the actual data model and presentation requirement is different.

Unfortunately, I need to use this in pivot table and the database is big and those values are in different tables and don't want to perform unnecessary resident load and etc.

Thanks,

Sandip.

[jagan]

Hi,

It is difficult to handle in front end.  Can you attach sample file?

Regards,

Jagan.

[#5]

Hi Jagan,

I don't have sample file at the moment.

Thanks,

Sandip.

[#6 · Accepted solution]

I think I have found the solution without using set analysis as below

sum({<BD=>}if(LBD=aggr(nodistinct only(COMPARE_LBD),[Metric Name]),[Metric Value]))

AS it seems to be working but if anyone can find set analysis solution for this than it will be great.

Thanks,

Sandip.