topic Help with Set Analysis syntax in QlikView

Help with Set Analysis syntax

Sunil_Kenth — Mon, 18 Jul 2011 14:05:19 GMT

Hi all,

I am trying set the expression for a Gauge chart to only calculate for the parent category of 'Telecoms' and for the current year and month.

I have setup variables for the month and year as follows:

vCurrentMonth = (Month(Max(Date))

vCurrentYear = (Year(Max(Date))

='£'& Num (Sum({$< [Parent Category] = {'Telecoms'}, Month = {$(=$(vCurrentMonth))}, Year = {$(=$(vCurrentYear))}>}[Money Out]))

The result i get for this expression is £0

Any ideas?

Thanks

Help with Set Analysis syntax

erichshiino — Mon, 18 Jul 2011 14:13:59 GMT

I guess you have a problem with the date format in set analysis.

In this case, you should create a field on script where month is a number, instead of a formated date.

The syntax on the script would be:

Load (...)

num(Month) as nMonth

from / Resident... etc

Then, the variable would be:

vCurrentMonth = num((Month(Max(Date)) )

Then, the expression would be:

='£'& Num (Sum({$< [Parent Category] = {'Telecoms'}, nMonth = {$(=$(vCurrentMonth))}, Year = {$(=$(vCurrentYear))}>}[Money Out]))

Maybe the expression can be simpler, like this:

nMonth = {$(vCurrentMonth)}

nMonth = {$(=vCurrentMonth)}

Hope this helps,

Erich

Help with Set Analysis syntax

Sunil_Kenth — Mon, 18 Jul 2011 14:57:00 GMT

Thanks Enrich - your answer prompted me to wrap a num function around the current month variable so I now have vCurrentMonth=num(Month(Max(Date)) and this now works.

Thanks for the quick response.

Sunil