Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

a question about counts

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!!!

1 Solution

Accepted Solutions
jsn
Honored Contributor

Re: a question about counts

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

8 Replies
jsn
Honored Contributor

Re: a question about counts

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.)

Not applicable

Re: a question about counts

Okay, let me try that. One sec.

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

Not applicable

Re: a question about counts

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.

jsn
Honored Contributor

Re: a question about counts

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

jsn
Honored Contributor

Re: a question about counts

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.

Not applicable

Re: a question about counts

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?

jsn
Honored Contributor

Re: a question about counts

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.

Not applicable

Re: a question about counts

It works! Thanks so much for the help.

Community Browser