topic set analysis -- clarification in QlikView

set analysis -- clarification

Wed, 15 Jun 2011 09:55:58 GMT

Hi All,

I am doing a Dashboard where I have to show a pivot table which has the data about the Plan Cost and Actual cost.Actual cost is working fine in the Table.I have a problem with the set analysis expression.

This is the onI have used in the chart

count({<Month_Cost = {'June'},Month= {'June 2011'},[Travel Mode] = {'Flight'}>}Cost_Targets) .I have two months in the table.

Month is the field present in the fact table.Month_cost is the field which is connected to the fact table using Region field.

I have used the

set analysis -- clarification

Anonymous — Wed, 15 Jun 2011 10:19:17 GMT

Hi There

You are trying to count where [Month] ='June 2011'?

Is that correct? Does your [Month] Field also contains the year?

Dennis.

Re: set analysis -- clarification

Wed, 15 Jun 2011 10:37:55 GMT

I will give you a brief description of what I have and What I need that might help you in resolving the problem.

I have a fact table named Trip details in that I have a field called Travel date. So using this travel date I have created one more table called Calendar which is joined to the Trips Details(Fact table).

I have one more field in the Trip details table named Region .I was given a excel with the region wise targets which looks like below

So I have used the cross table to load this data. I have connected the above table to the Fact table using Region field.

So now my requirement Is I want to show a pivot table with actual and plan values – region wise.I have the quarter, month and region as the dimensions.

Yes I have created the month field in such a way that it gives me the year info also.

month(TRAVEL_DATE) &' ' & year(TRAVEL_DATE) as Month,

In this I am getting the actual cost fine.I have a problem with the Planned cost.

It should show me the values of the targets which are defined in the first attached table.So for this I have written the set analysis.Might be what I have written is wrong.Please correct me if it is wrong and let me know how to get the correct figures for the planned cost.

Thanks in advance.

Re: set analysis -- clarification

Wed, 15 Jun 2011 11:10:58 GMT

Hi All

Any one have any Idea regarding this Set analysis Issue…

Re: set analysis -- clarification

Anonymous — Wed, 15 Jun 2011 11:18:09 GMT

Shouldn't you use SUM instead of COUNT?

Count will return the nummer of time there are Cost_Targets's

SUM will return the totaal of Cost_Targets

Anyway, about your set analysis;

Without having your data or file I can't tell where your expression is wrong, there is no error as far as I can see

I would suggest that you cut it down in and see which of the following expressions does not return what you should expect

count({<Month_Cost = {'June'}>}Cost_Targets)

count({<Month= {'June 2011'}>}Cost_Targets)

count({<[Travel Mode] = {'Flight'}>}Cost_Targets)

I guess it's a typo or maybe a format issue.

Re: set analysis -- clarification

Wed, 15 Jun 2011 12:20:15 GMT

I have attached the application. Please have a look at that….

Re: set analysis -- clarification

Anonymous — Wed, 15 Jun 2011 13:05:37 GMT

No application found...

Re: set analysis -- clarification

Wed, 15 Jun 2011 13:24:21 GMT

Please let me know if u find any solution to the problem

Re: set analysis -- clarification

Anonymous — Wed, 15 Jun 2011 13:31:48 GMT

I can't find your QV-document but did you try the following expressions:

count({<Month_Cost = {'June'}>}Cost_Targets)

count({<Month= {'June 2011'}>}Cost_Targets)

count({<[Travel Mode] = {'Flight'}>}Cost_Targets)

Re: set analysis -- clarification

Wed, 15 Jun 2011 13:47:11 GMT

PFA…

I was not able to send you through mail so what I have attached the application in the qlikview community.

Re: set analysis -- clarification

Wed, 15 Jun 2011 13:50:58 GMT

If you are not able to find the QV document I have attached the application in the Qlikview community.please find the same with below subject line…

set analysis Clarification <http://community.qlik.com/message/124425#124425

Re: set analysis -- clarification

Wed, 15 Jun 2011 13:52:11 GMT

Yes I used all the expressions that you have mentioned. I am attaching the application again..

Re: set analysis -- clarification

Anonymous — Wed, 15 Jun 2011 13:53:33 GMT

Like I thought, a typo 🙂

Month= {'June 2011'}

Must be

Month= {'Jun 2011'}

Re: set analysis -- clarification

Wed, 15 Jun 2011 13:57:25 GMT

I have given in the correct format… but dnt know why it is not coming… Do you have any idea about using the ONLY function here….

Re: set analysis -- clarification

Anonymous — Wed, 15 Jun 2011 14:04:29 GMT

If you change this:

sum({<Month_Cost = {'June'},Month= {'June 2011'},[Travel Mode] = {'Flight'}>}Cost_Targets)

to this

sum({<Month_Cost = {'June'},Month= {'Jun 2011'},[Travel Mode] = {'Flight'}>}Cost_Targets)

It works, right?