understanding when to use < or } in formula, qlikview

[Anonymous]

Hi,

I need to pick up all transactions within the current Financial Year that are not blank.  i have the following formula but it is not working (no error just zero shows up).  A little red line shows up under the "}" after FiscalYear.

if(ContactOptions <> '',Count({<%DateEmpFormAdded = FiscalYear>}) EngagementType)

Any assistance appreciated.

Also can anyone direct me to a youtube or page where it explains the use of the following characters in a formula?

}

]

,

etc....

[sunny_talwar]

Or may be like this:

If(ContactOptions <> '', Count({$<%DateEmpFormAdded = {"$(='>=' & Date(MonthStart(Today()), 'DD/MM/YYYY') & '<=' & Date(Today(), 'DD/MM/YYYY'))"}>} EngagementType)

Here you get the range of date for today's month using this

='>=' & Date(MonthStart(Today()), 'DD/MM/YYYY') & '<=' & Date(Today(), 'DD/MM/YYYY')

[Anonymous]

Hi Sunny,

as a rule do you always need to set "to" and "from" ranges.  I.e. for the following example i want to pick up any transaction in the last week.  Could i just not use the following(i havent got it working yet though):-

Count({$<%DateEmpFormAdded ={"$(='>='&Date((Today()-7),'DD/MM/YYYY')"}>} EngagementType)

ive checke the :-

Date((Today()-7),'DD/MM/YYYY')  i an object box and it works

[sunny_talwar]

You don't have to, but there are occasions when you might need it. For instance if you have data beyond today and you only want to view last one week then you would need 'to' to 'from'. But if you only have data up untill today then you don't really need it.

Count({$<%DateEmpFormAdded ={"$(='>=' & Date(Today()-7, 'DD/MM/YYYY'))"}>}EngagementType)

You seem to have missed one parenthesis at the end. Try this now

[Anonymous]

Hi Sunny,

i am trying to get the tool to work but meanwhile i am using your text box trick to test dates.

I am using this:-

(ContactOptions <> '', Count({$<%DateEmpFormAdded = {"$(='=' & [FiscalYear])"}>} EngagementType))

it only gives me half the  count but i cant work out why.  could it be bc DateEmpFormAdded is format dd/mm/yyy and year is yyyy?  if so how do i get around this ?

[sunny_talwar]

First, you cannot compare apples to oranges with set analysis. Dates are different than year field. You can create a range for fiscal year start and end, but FiscalYear alone cannot work. Then second comes the formatting issue. You do need to make sure that LHS and RHS of set analysis always have the same date format for the set analysis to work.

[Anonymous]

Hi Sunny,

i am using the following formula:-

(ContactOptions <> '', Count({$<%DateEmpFormAdded = {">=01/07/2015<=$(=Date(Today()))"}>} EngagementType))

If there are two rows with the same date (for eg below),  Qlikview is only counting it as "1" rather than what it should be "2"?

E.g.

Date               EngagementType

21/05/2015    Prospect

21/05/2015    Prospect

Any idea why it does this plesae

[Peter_Cammaert]

The Count() function will only act like that when you add the DISTINCT keyword. With DISTINCT, it will count just the distinct EngagementType values. Without the DISTINCT keyword, it will count all occurrences of any EngagementType value, i.e. all the rows that are selected by the set analysis expression.

Your data may display double values in a table box, but your data model may only have one. Check your data model.

Peter

[Anonymous]

I’ve checked SQL as well as the table box and the result set is definitely wrong

[sunny_talwar]

is it possible that Date and Engagement type are coming from different tables in your data model?

[Peter_Cammaert]

That's why I suggested to check your data model, and not a user interface object like a table box. A table box will show all field value combinations and may repeat a field value many times, even if it is present in your data model only once.

For example, a dimension table with customers will contain every Customer Name only once. The count(CustomerName) will produce 1 if you select 1000 Orders by a single customer.

Peter