topic Set analysis help? in QlikView

Set analysis help?

Wed, 20 Jul 2011 10:47:27 GMT

Hello Experts,

I am trying to write a simple expression where when a user selects year dimension, it should show the December data, likewise if he selects 3 months (eg: Aug, Sep, Oct), the last month 'Oct' of his selection should show the data.

year and month are my dimensions...

I am trying the below expression... can anyone help with this please....

=if($(vExpressionCond),

(sum({<month = {'=$max(month)'}>}reportValue)<>0,

num

(sum({<month = {'=$max(month)'}>}reportValue),

$(vNumberForm)),''))

Thanks in advance,

Mady

Set analysis help?

swuehl — Wed, 20 Jul 2011 10:57:26 GMT

Hi,

I think the dollar sign expansion inside your set expression is not correct,

try

sum({<month = {$(=max(month))}>}reportValue)

Stefan

Set analysis help?

Wed, 20 Jul 2011 11:08:02 GMT

Stefan, Thanks for the quick response, but its not working. it always shows 0...any other suggestions please?

Re: Set analysis help?

swuehl — Wed, 20 Jul 2011 11:13:08 GMT

Hi,

if it is possible, please post a sample application with your problem.

To pin point your problem, it is best to build it up from bottom to top, i.e. please try the sum expression itself, then add format, then ExpressionConditon.

What kind of data format is month? Have you tried with a literal comparison instead?

Regards,

Stefan

Set analysis help?

Wed, 20 Jul 2011 11:18:05 GMT

Hello Stefan,

Month is in number format as when i do a max(month) and select year = 2010, it returns 12 (december). And likewise when I select Jan, Feb, March it shows me 3(March) its exactly doing what I want, But how do I use this variable or expression in Set Analysis.

Cannot post an example as my app is big and highly confidential.

Thanks Again,

Mady

Set analysis help?

swuehl — Wed, 20 Jul 2011 11:34:13 GMT

Hi,

so you get a result when using sum(reportValue), right?

Do you get a result for sum({<month={12}>} reportValue) ?

(or any other value?)

month is a dimension, right?

AS I said, please try to use this expression stand alone first, e.g. in text box or chart expression, without further formatting expressions or conditions.

I think the expression I posted is corrected, but try to build a small app myself.

Stefan

Re: Set analysis help?

swuehl — Wed, 20 Jul 2011 11:41:40 GMT

Please find attached my simple example.

Stefan

Set analysis help?

Wed, 20 Jul 2011 11:48:25 GMT

Ok my month dimension is like jan, feb, mar, apr etc.... but when i do a max(month) in a text box and select year 2011 it shows the max(month) = 7 and when I select year 2010, itshows the max(month) as 12 which is december... How can I make it work in set analysis??

When I try sum({<month={12}>} reportValue), it wont work...thats the struggle I have..its strange when i select year the max(month) shows the correct number...

Any help or suggestions please?

Set analysis help?

swuehl — Wed, 20 Jul 2011 12:01:37 GMT

How do you generate month? Might be an idea to have a date format instead, or using a numerical monthID linked to the month (name).

Stefan

Set analysis help?

Wed, 20 Jul 2011 12:22:17 GMT

year and month is generated from a date_value field which is in the format of dd/mm/yyyy 00:00:00 and when I do a max(date_value) select year 2010, it returns 31/12/2010 00:00:00 which is exactly what I want, just that when the end user selects year 2010, it should by default show Dec data....

Thanks Again

Re: Set analysis help?

swuehl — Wed, 20 Jul 2011 12:44:45 GMT

I currently also struggle with month as name and max(month)...

I got it working with a additional monthID, please see attached.

Stefan

Set analysis help?

Wed, 20 Jul 2011 12:49:41 GMT

Stefan,

In your application, when i select a year 2010 it shows all months except Jan... I want to see Dec selected when I click on 2010 and like wise, if i select feb, mrz, apr then I should see Apr as this is the maximum from what i selected.. I have the same situation what you have tried here and I can see the number, but not able to make it work.

Thank you again for your time and patience, any more suggestions please?

Set analysis help?

swuehl — Wed, 20 Jul 2011 13:09:56 GMT

Hi Mady,

in my example, the expression we discussed affects only the textbox ("Summe =" / sum of values).

If you select a year, the sum is correctly displaying only dec value.

I always understood that this was your requirement.

So you want also preselect within listboxes?

Would really be good if you could post an example or rework mine...

Regards,

Stefan

Set analysis help?

Wed, 20 Jul 2011 14:07:55 GMT

Hello Stefan,

Thats exactly what I was looking for but was confused by looking at the title of the chart columns. Can you help me set up the title for the charts... I mean when I select the date(max(month_num),'MMM') expecting to see the name of the month, it returns Jan instead of Dec... any suggestions please..

Previously I was confused because of the chart Titles... but I am half way through with the issue...

Thanks again for all your time spent.

Set analysis help?

Wed, 20 Jul 2011 14:29:22 GMT

Hello Stefan,

Nevermind I found the solution to use it as a Header in chart and solved it. Below is the expression if you need it for reference.

='Month Actual ' & pick(max(month_num),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') & ' ' & year

Thanks again for all your time and effort.

Mady

Set analysis help?

swuehl — Wed, 20 Jul 2011 14:35:22 GMT

Sorry, I got lost in translation...

Do you mean, if you select 2012, ypu expect Dec but you are actually getting Jan?

Thats because my Dates range from Feb 2010 to Jan 2012 (I forgot a -1 in my script, autogenerating the Dates).

Is that's what is confusing you?

Stefan

Set analysis help?

Wed, 20 Jul 2011 15:15:58 GMT

Hello Stefan,

No even in my script, when I am using the expression for chart Title, it shows me max(month_ Num) as 12, but the name as Jan instead of December, which was annoying so I have kind of hard coded it for now and solved the problem. I was not talking about your application months.

Thanks Again,

Mady