topic Re: Set analysis with aggr fuction in QlikView

Set analysis with aggr fuction

Wed, 27 Aug 2014 00:44:41 GMT

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,

Re: Set analysis with aggr fuction

jagan — Wed, 27 Aug 2014 01:49:33 GMT

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.

Re: Set analysis with aggr fuction

Wed, 27 Aug 2014 02:53:39 GMT

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.

Re: Set analysis with aggr fuction

jagan — Wed, 27 Aug 2014 05:30:37 GMT

Hi,

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

Regards,

Jagan.

Re: Set analysis with aggr fuction

Wed, 27 Aug 2014 13:16:29 GMT

Hi Jagan,

I don't have sample file at the moment.

Thanks,

Sandip.

Re: Set analysis with aggr fuction

Wed, 27 Aug 2014 20:36:18 GMT

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.