Correct way to describe a chart->listbox dimension by month name

[Anonymous]

I am trying to do the following:

1) Create a chart->list box.

2) I am going to click the horizontal property on the presentation.

3) On the page I have a start date (say, July 19, 1017)   and an end date (say Nov. 22, 2017).

4) Have a number of expressions that list data counts of different things between those two date ranges.

5) I want to show those counts for each of the months from the start date month to the end date month, so in my case, 5 columns

   July 2017, Aug 2017, Sep. 2017, Oct 2017 and Nov 2017

6)  I have tried an expression for the dimension of ='>=' & Date(vStartDate) & '<=' & Date(vEndDate)  and then

='>=' & monthname(Date(vStartDate) & '<=' & Date(vEndDate))     but whenever I say ok to this,  qlikview  grayw out my listbox while it attempts to calculate things.      And if it finishes,  nothing is correct.

I feel I am close on the calculation,  but need some help,  or a reference to something in youtube that might help

Thank you

Message was edited by: Arnold Bearak Enclosed is a sample qvw file.  I have to chart-> listboxes.  The one by offer is all set. The one I am having a problem with is the 'Test Results by date. What I would like to have is 5 columns by months  7-17  8-17 9-17 10-17 11-17  with the results for each month shown for the 3 categories of stats with the counts for just that month.    So that the total  value of the combined months  matches the number in the total column. Hope this additional information yields to more information.

[sunny_talwar]

It seems you are comparing BoP and EoP to SignupDateM, if that is correct, may be you need to use IntervalMatch

[Anonymous]

So, can I use interval match for SignupDateM to Bop/Eop

and interval match for cancelDateM2 to Bop/Eop?

That is,  and I set up multiple interval matches to the same BoP/EoP pair?

Thanks

[sunny_talwar]

I think you do have to use IntervalMatch somehow, but in order to give you the result you want, will have to think a little. Would you be able to share the csv files you have used in your sample?

QlikTest.csv

Months.csv

theDate.csv

[Anonymous]

I have done so.

Thanks

[sunny_talwar]

checking

[Anonymous]

I think the following link,

QlikView App: Dates, Date Ranges and Set Analysis

might provide a lot of information for me on what I want to do.

I have to figure out how to use something like this,  replacing the calendar year with an expression that would calculate the months from the Beg of Period month to the End of Period month,  so for July xx thru Nov xx that would be five months. 

What do you think Sunny?

[sunny_talwar]

It might, but before anything else, you need a better data model. The way your model is right now, it is going to give you a lot of problems with regards to complicated and slow running expressions. Once you have your data model sorted out, a lot of your issues are going to go away.

[Anonymous]

I am looking into this link

Linking to two or more dates

As a way to connect the dates in my various tables (in the example on this ticket,  there are only two dates) to the beg. of period table and end of period table.  Do you think this will help improve the data model you referred to?

[sunny_talwar]

I have been looking at this for 2 days now without going anywhere. I think we need to take a step back and if you could explain the business requirement, that might help us attack this much better.

[Anonymous]

I will try to explain here the goals of what I am trying to do.

I have a set of 'partners'   that provide services to its customers.

Each partner has bundles of services that it can sell to its customers.

Each bundle of services is called an 'offer'.

Each Offer has various attributes.  Offer Id,  Monthly Cost are two of them.

Each Customer has various attributes associated with it.

These attributes include things like 'Offer Id',  'SignUp Date',  'Cancel Date' if they cancelled the service,

Registration Date.   There are also Payment or Credit dates saved associated with the Customer.

My clients who are using my qlikview  data are requesting that I give them the opportunity to view the data in 3 different ways.   All three ways require the qlikview client to select a start date (Beg of Period  - BOP ) and an end date (End of Period-EOP)

The data being displayed includes things like 'The number of New Signups in the period (signup date between BoP and EoP).  Number of Active members in period (Number of people who signed up within the period and who did not cancel before the EoP).  There is information on # of customers who Registered within the period,  and how many payments were made in the period etc).

The first display method is to allow the client to choose the partner for which data is requested and then one or more offers related to that partner.  The displayed output should show information with the Offer ID as the dimension, and counts as to how many customers fit the parameter in question.  I have been able to do this as a chart->listbox with the Presentation field 'horizontal box' checked off.

The second method of display, the one I am unable to figure out how to do is to allow the client to select a Partner and and Offers as well as the BOP and EOP and have the information displayed totals for all offers combined,  but to display this information on a month to month basis for the months that fit between the BoP and EoP.

For instance,  if my BOP is July 19, 2017 and my EOP is Nov. 30, 2017,  the dimension would be a month expression  that would allow the output to show the monthly values for each expression in my list.  I assume again the Horizontal box would beon the Presentation page.   

The 3rd presentation requested is to have an output that allows the client to see monthly information by the individual offers chosen.   I think depending on the complication of #2,  I can get away with not providing this last choice.

I am working at depricating a tool that allowed all of these options.  The difference between using Qlik and the tool that I would be depricating, is that tool performed live queries,  so it can create a number of queries and provide the results requested.  With Qlikview,  I am working with static data that is collected daily.

I hope this provides more information for people who might be thinking about this.

Thanks,

Arnie