topic Re: Set Analysis Help in QlikView

Set Analysis Help

Mon, 08 Jul 2013 16:42:37 GMT

Hi,

I am not too sure how to use set analysis properly and have been trying to put it into this formula:

(

sum(if(year(TransactionDate)=Year(today()),[Total Incurred],0)))/

(sum(

GrossPremium

*

(

if(Year([Day Earnt])=Year(Today()),[Earn Ratio],0)

)))

however i am not haviing any luck.

If someone could point me in the right dierection that would be great.

Many thanks.

Re: Set Analysis Help

Sokkorn — Tue, 09 Jul 2013 01:08:13 GMT

Hi Sir,

I would suggest to create a new fields for set analysis, something like

[Table]:

LOAD

...

Year([TransactionDate]) AS [TranYear],

Year([Day Earnt]) AS [DayEarntYear],

...

Then use set analysis below:

Sum({$<[TranYear] = {$(=Year(Today()))}>} [Total Incurred])/Sum({$<[DayEarntYear] = {$(=Year(Today()))}>} [Earn Ratio]*[GrossPremium])

Regards,

Sokkorn

Re: Set Analysis Help

Tue, 09 Jul 2013 08:13:19 GMT

Thanks for your response.

How would i go about putting another condition in this e.g. colour = green?

Re: Set Analysis Help

Sokkorn — Tue, 09 Jul 2013 08:16:34 GMT

Hi,

If you want to add more condition, then try

Sum({$<[TranYear] = {$(=Year(Today()))}, [Colour]={'Green'}>} [Total Incurred])/Sum({$<[DayEarntYear] = {$(=Year(Today()))}, [Colour]={'Green'}>} [Earn Ratio]*[GrossPremium])

Make sure you are trying to add more condition not Text colore.

Regards,

Sokkorn

Re: Set Analysis Help

Tue, 09 Jul 2013 08:26:21 GMT

Sum({$<[TranYear] = {$(=Year(Today()))}, [Colour]={'Green'}>} [Total Incurred])/Sum({$<[DayEarntYear] = {$(=Year(Today()))}, [Colour]={'Green'}>} [Earn Ratio]*[GrossPremium])

There seems to be a bit of a problem with the }, after year(Today()))) Its comes up with error in set modifier expression

Re: Set Analysis Help

Sokkorn — Tue, 09 Jul 2013 08:30:08 GMT

Hi Sir,

Does it work or not? and can you attached sample app?

Regards,

Sokkorn

Re: Set Analysis Help

Tue, 09 Jul 2013 11:24:14 GMT

Hi,

I have just tried this

Sum

({$<EventTransactionDateYear={$(=Year(Today()))},[Colour]={Green}>} [Total Incurred])/

Sum({$<DayEarntYear={$(=Year(Today()))}, [Colour]={Green}>} [Earn Ratio]*[GrossPremium])

In the expression editor it says 'Expression OK' however when you click ok it says taht no data cannot be displayed in the chart.

Re: Set Analysis Help

er_mohit — Tue, 09 Jul 2013 11:38:02 GMT

Sum({$<DayEarntYear={"$(=max(DayEarntYear))"}, [Colour]={Green}>} [Earn Ratio]*[GrossPremium])

Sum({<DayEarntYear={$(=year(date(today()))}, [Colour]={Green}>} [Earn Ratio]*[GrossPremium])

Re: Set Analysis Help

Sokkorn — Tue, 09 Jul 2013 11:43:52 GMT

Hi,

Use [Colour]={'Green'} not [Colour]={Green}

Regards,

Sokkorn