Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

set analysis -- clarification

 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


<SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;">

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.


<SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;">


<SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a" mcestyle="font-size: 8pt; color: #5a005a;">


<SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a; FONT-FAMILY: " mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a; FONT-FAMILY: " mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a; FONT-FAMILY: " mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a; FONT-FAMILY: " mcestyle="font-size: 8pt; color: #5a005a;"><SPAN style="FONT-SIZE: 8pt; COLOR: #5a005a; FONT-FAMILY: " mcestyle="font-size: 8pt; color: #5a005a;">I have used the



 


 


 








 


 































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































 

Tags (2)
14 Replies
dennisnet
Valued Contributor III

set analysis -- clarification

Hi There

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

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

Dennis.

Not applicable

Re: set analysis -- clarification

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.

Not applicable

Re: set analysis -- clarification

Hi All

Any one have any Idea regarding this Set analysis Issue…

dennisnet
Valued Contributor III

Re: set analysis -- clarification

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.

Not applicable

Re: set analysis -- clarification

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

dennisnet
Valued Contributor III

Re: set analysis -- clarification

No application found...

Not applicable

Re: set analysis -- clarification

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

dennisnet
Valued Contributor III

Re: set analysis -- clarification

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)

?

Not applicable

Re: set analysis -- clarification

PFA…

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

Community Browser