topic Set analysis - set KPIMonth to dimension month?? in QlikView

Set analysis - set KPIMonth to dimension month??

Anonymous — Mon, 28 Jun 2010 13:47:11 GMT

Hi all,

I have a table which is not coupled (since qlikview is getting circular problems when coupling the table) ...
The table contains KPI's for various checking on various tables.


KPI KPIMonth Value
1 1 23
1 2 24
2 1 1.25
2 2 1.45
3 1 98
3 2 98

Now I try to get the correct KPI value for comparing (that is the tables purpose) ...


sum({<KPI = {3}, KPImonth = {"=$(Month)"} >}if((networkdays(Serviceorders.OrderDate, SendingDate) ) < value,1,0))

Month in the sample is the dimension month ... but somehow it seems that this is not working ...

The Serviceorders.OrderDate and SendingDate and Month are from our ServiceOrders table in this case.

Anyone got suggestions how to solve this one?

Set analysis - set KPIMonth to dimension month??

fernandotoledo — Mon, 28 Jun 2010 15:14:04 GMT

I think that you should spend a little more time trying to link all the tables in your datacloud and clear the circular reference issue.

This kind of not coupled tables solutions lacks performance in middle to high size QVW and the expressions always becomes too complex.

Why dont you try to create a LinkTable between your data and kpi data?

Best regards

Fernando

Set analysis - set KPIMonth to dimension month??

johnw — Mon, 28 Jun 2010 23:19:27 GMT

A set in set analysis is only analyzed once for the entire chart, not once for each row of the chart. So unless you select only a single Month, I wouldn't expect set analysis to work. I believe you have to use an if() for multiple rows.

sum({<KPI={3}>} if(KPIMonth=Month...))

Performace could be pretty bad if you have a lot of data. But I'm guessing you only have KPIs for whole months, not for dates or timestamps, and linking Month to KPIMonth probably won't be too bad. I wouldn't do it at the date level, though.

Set analysis - set KPIMonth to dimension month??

Tue, 29 Jun 2010 00:38:22 GMT

Hi, I believe you can distribute your KPI among the months selected, using the concat function within a variable.

Declare a variable named vMonthYear and set the content to something similar to this:

=concat(distinct chr(39) & MonthYear & chr(39),',')

Then in your chart add the KPIMonthYear dimmension. Then use set analyis to limit the KPIMonthYears to the ones available or selected by the users:

Exp:

sum({$<.... KPIMonthYear = {$(=vMonthYear)}>} fields...)

That should do the trick.

Regards