topic Re: a question about counts in QlikView

a question about counts

Thu, 21 Feb 2013 01:04:14 GMT

So, I want to make a chart.

on the Y axis, I want to have percentages. 10% increments, from 0% to 100%.

What I want to calculate... this is where I have an issue.

I have a field called "sent_date", and a field called "received_date". These all have a unique identifier called a "apl_id". So, each apl_id has a date it was received by my company, and a date where it was sent out to the member. They must be sent out within 30 days of receiving them.

If, let's say, for the month of October, we have 100 apl_id's, with 100 received dates and 100 sent dates, if all 100 were sent out within 30 days of receiving them, the chart would show 100% for the month of October. If one was outside of the 30 days, it would need to show 99%.

I haven't a clue how to do this calculation inside of Qlikview.

Is there a way to have a chart do the calculations behind the scenes, and then present them in the chart as a percentage?

I suppose it would need to do an IF statement, like "IF >= 30, then count it as a 1. If not, count as a 0 (or dont count at all).... then you would put this inside of the chart?

Please help!!!

Re: a question about counts

Anonymous — Thu, 21 Feb 2013 01:13:59 GMT

Something like this should work:

Dimension: Month

Expression: sum(if(sent_date-received_date<=30,1))/count(apl_id)

Then under the Number tab, set your expression to Show in Percent.

(if you have lots and lots of those apl_id's you might want to set a flag as 1 for each instance in the script and then use sum(apl_flag) instead of count(apl_id) but for a normal dataset you shouldn't need to worry.)

Re: a question about counts

Thu, 21 Feb 2013 01:15:49 GMT

Okay, let me try that. One sec.

(oh - I should have said Y axis, not X.)

Re: a question about counts

Thu, 21 Feb 2013 01:19:09 GMT

Ugh. "no data to display".

Am I doing this right? I'm just creating a bar chart, and selecting the month field that I have, then entering the expression that you wrote.

Re: a question about counts

Anonymous — Thu, 21 Feb 2013 01:49:14 GMT

One thing I can think of is that your dates are perhaps not formatted in a way that QlikView will recognize as dates? In this case it won't be able to subtract the received date from the sent date.

You might want to take a look here for a date explanation:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

Re: a question about counts

Anonymous — Thu, 21 Feb 2013 01:50:40 GMT

Also, in your other thread the sent date is called apel_ltr_snt_dt in which case the syntax in my previous example is wrong. Make sure that the fieldnames in the expression matches what you have in your data model.

Re: a question about counts

Thu, 21 Feb 2013 01:53:07 GMT

oh, yeah I'm double checking to make sure I have the fields correct

Let me check the dates to make sure they can be calculated.

Thanks for the help - was I missing anything in the way I was going about setting up the bar chart, other than these things?

Re: a question about counts

Anonymous — Thu, 21 Feb 2013 02:08:30 GMT

No, not really.

All you need to do is pick the bar chart, pick the dimension and create an expression and you should be up and running. The rest is mostly for changing the look and feel, sorting, labels etc.

Re: a question about counts

Thu, 21 Feb 2013 18:44:01 GMT

It works! Thanks so much for the help.