Re: set analysis -- clarification - Qlik Community

Report Inappropriate Content · ‎2011-06-15

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

Anonymous · ‎2011-06-15

Hi There

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

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

Dennis.

Report Inappropriate Content · ‎2011-06-15

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.

Report Inappropriate Content · ‎2011-06-15

Hi All

Any one have any Idea regarding this Set analysis Issue…

Anonymous · ‎2011-06-15

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.

Report Inappropriate Content · ‎2011-06-15

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

Anonymous · ‎2011-06-15

No application found...

Report Inappropriate Content · ‎2011-06-15

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

Anonymous · ‎2011-06-15

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)

?

Report Inappropriate Content · ‎2011-06-15

PFA…

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