Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Clarioner
New 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 (4)
3 Replies

Re: Question about condition SUM/COUNT

Correct Set Analysis syntax is

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

Re: Question about condition SUM/COUNT

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}

Highlighted
MVP & Luminary
MVP & Luminary

Re: Question about condition SUM/COUNT

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