Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Clarioner
Contributor
Contributor

Question about condition SUM/COUNT

Hi all,

I'm still getting my head around applying conditions to functions on chart objects... also variables. Anyone help with the following:

I have an ODBC database, which returns all records in the dataset. I want to filter this data for specific chart objects/kpis.

Examples:

I want to show a count of order stah ave been invoiced. The database contains a field INVOICEDFLAG - which will be set to 1 (if invoiced), or 0 if not. I am using the following in the expression for the Measure. It returns no data, and I know there is data. What am I doing wrong?

COUNT({[INVOICEDFLAG = 1]} NUMBER)

also tried this:

COUNT({[INVOICEDFLAG = <{1}>]} NUMBER)

NB: NUMBER is the job-number, which is unique, soa count on this returns the number of records. I have similar situation with SUM()

SUM({[INVOICEDFLAG = 1]} SELLINGPRICE)

Another issue. I am trying to lock a charts to show the number of orders (and value) received Today, this week, this month. I can create the KPI no problem, but I cannot figure out the limit the data iNcluded to for example, this month. IT seems there is no one function that takes account of current month AND year... you have to do two tests, and even then I can;t get it to work

I have tried:

COUNT({[MONTH(DATEIN) = MONTH(TODAY()) AND YEAR(DATEIN) = YEAR(TODAY())]} NUMBER)

(This doesn't work)

I can't believe stuff like this is so hard to do... it must be really common. Even the syntax is really complex and could be so much easier.

Question:

At what stage are variables I create evaluated. So as an example:

IsInvoiced = IF(INVOICENO > 0,1,0)

At what point is this evaluated?... once?, or for each record? - as I want to use this variable in an filter expression.

Question:

How can I create a picker, based on dates whereby if I choose the Month-name, this implicitly means the current year, not just that month-name from ANY year?

 

Thanks in advance, sorry for my ignorance.

 

 

Labels (2)
3 Replies
sunny_talwar

Correct Set Analysis syntax is

COUNT({<[INVOICEDFLAG = {1}>} NUMBER)
Clarioner
Contributor
Contributor
Author

Thanks, that did work, although I missed off the ] o the fieldname. Please if you can:

Why do I need { } around the 1 in this expression why not just [INVOICEDFLAG] = 1?

Also: If I replace the = with > I get an error, yet > is a valid expression symbol?.. so how can I do a "greater than" test
e.g. [INVOICEDFLAG] > {0}

marcus_sommer

I think the best explanation what set analysis is and how it worked could you find here and there in links at the bottom:

A-Primer-on-Set-Analysis

- Marcus