Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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
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.)
Okay, let me try that. One sec.
(oh - I should have said Y axis, not X.)
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.
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
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.
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?
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.
It works! Thanks so much for the help.