topic what is wrong with set analysis in QlikView

what is wrong with set analysis

Wed, 24 Feb 2010 15:28:47 GMT

I have a plot chart with 2 dimension Annee & Mois

I want to calculate for each month an index based on january (Mois=01)

so my expression is:

Sum ([CA HT en euros]) / Sum ( {<Mois=01>} [CA HT en euros])

But no chart is displayed.

In my input table the field Mois take the value 01, 02 , ...., 12

Thanks in advance for your help

Jean-Jacques

Wed, 24 Feb 2010 16:04:41 GMT

I'm guessing those months are strings as they have a leading zero. Try:

Sum ([CA HT en euros]) / Sum ( {<Mois='01'>} [CA HT en euros])

This expression is respecting the dimension for the record it is in. I'd guess you want something like:

Sum ([CA HT en euros]) / Sum ( {<Mois='01'>} TOTAL [CA HT en euros])

That will get you the sum for whatever dimension you have divided by the total for January, ignoring the dimension.

Wed, 24 Feb 2010 17:09:59 GMT

Thanks NMiller for your answer but it didn't work. You guessed well about the calculation.

As i have also the year in the file, i created a new field as :

Monthname( Makedate(Annee , Mois)) as MonthName ,

How can write the expression using MonthName = January ? I'm sure you have an idea and allow to bypass the string for Month.

Miguel_Angel_Baeyens — Wed, 24 Feb 2010 17:43:29 GMT

Hello Jean-Jacques,

If you have set a MonthNames variable in your script (it is by default), you may use for your set analysis something like

Sum ( {<Mois = {$(=Subfield(MonthNames, ';', Num(Mois)))} >} [CA HT en euros])

where the subfield function will return your default month name...

Wed, 24 Feb 2010 18:17:00 GMT

Maybe something like:

Sum ([CA HT en euros]) / Sum ( {<MonthName='Jan'>} TOTAL [CA HT en euros])

You'll need to verify the format of your MonthName field and format the set modifier accordingly.