I am new to Qlikview and this forum has really helped me a lot in understanding the functionalities. I am trying to display revenue records over months. The multibox selection consists of month and year in dropdown. I have a slider which should allow me to display the dates for the month selected in the selection box.
Can somebody help me with this? I have tried using the expression from the drop down in the fields box in the slider properties window.
=only(if(ORDER_DATE>=date(date#([MONTH],'MMM'),'MM/DD/YYYY') and (ORDER_DATE<=MonthEnd(date(date#([MONTH],'MMM'),'MM/DD/YYYY'))),ORDER_DATE) )
This should display only the dates between the start date and end dates of the particular month selected (in this case the [MONTH] field).
Any suggestions would be highly useful as I am not able to display apt records in the charts due the dates not being selected.
I am trying to use the MonthStart and Monthend functions in the expression but the slider does not display it.
Also, MONTH and ORDER DATE are different fields so I have to decide on the order dates in the month selected.
I am also facing a problem converting the MONTH string to a date. The MONTH value is in the format 'MMM'('AUG') but when i use the date functions, it is not recognizing it. By your explaination, I must put in the minimum value and maximum value but they are not highlighted when I select a field. They are displayed only when I use a variable.
I maybe missing out on certain things but your inputs would be really helpful in understanding the functionalities better.
Your selections are not working mainly becasue of those different unrelated fields.
I think that for your model is worth writing a master calendar. Basically, is one additional table that taking your master date field (in this case it could be Order Date) creates a handful of other valuable dimensions such as month, year, week, or any other expression you would like to create, related to time and useful for any selection regarding calendars.
Please find some useful code here and more elaborated discussion here, but there are many others browsing the blogs and the forums.
My Dates are related to each other. I have formed the month, year from the order date field as shown in the code below. I have also attached the qvd file for your reference. I get the order dates on the slider but they are for the whole year. I am not able to form the expression to display the dates within the particular month.
select o.order_date,to_char(o.order_date,'YYYY') as Year, to_char(o.order_date,'Mon') as Month,o.direct_channel, decode(o.flash_category,'Insurance','All Other Home Value Services','Product Warranty','All Other Home Value Services',o.flash_category) flash_category, o.placed_order_count, i.invoiced_order_count, i.invoice_revenue
WHERE TRUNC (rt.create_date) BETWEEN TO_DATE ('01/01/2010','MM/DD/YYYY') AND to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY')
AND rt.entity_oid = qcp.entity_oid
AND rt.scp_oid = qscp.oid
and qcp.direct_channel in ('Direct Marketing','Channel Marketing')
GROUP BY trunc(rt.create_date), qcp.direct_channel, qscp.qc_flash_sp_type) i
where o.direct_channel = i.direct_channel (+)
and o.flash_category = i.flash_category (+)
and o.order_date = i.inv_order_date (+)
select i.inv_order_date order_date,to_char(i.inv_order_date,'YYYY') as Year, to_char(i.inv_order_date,'Mon') as Month, i.direct_channel, decode(i.flash_category,'Insurance','All Other Home Value Services','Product Warranty','All Other Home Value Services',i.flash_category) flash_category, o.placed_order_count, i.invoiced_order_count, i.invoice_revenue
I've seen that you have a lot of tables creating a lot of synthetic keys. I don't know whether you want your model with them.
Anyway, I understand that you want a slider with all possible days within the month and year selected by user, is that correct? If so, you can create a new field, Day, using Day() function that extracts the day from the passed timestamp, and use it for the slider. You don't need to create a minimum or maximum date as your charts will dynamically change if a user change a selection.
Think about the master calendar in qlikview as it seems easier to change than your code here.