topic Slider value based on multibox selection. in QlikView

Slider value based on multibox selection.

Thu, 09 Sep 2010 15:03:29 GMT

Hi...

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.

Thank you.

Regards

Kumar

Slider value based on multibox selection.

Miguel_Angel_Baeyens — Thu, 09 Sep 2010 15:23:45 GMT

Hello Kumar,

If the user has first to select from the list one month, and having that month linked with the date field, the slider may have as minimum value

=MonthStart(DateField)

And monthend for the maximum value.

Hope that helps

Slider value based on multibox selection.

Thu, 09 Sep 2010 15:39:42 GMT

Hi Miguel,

Thanks for the prompt reply.

From your reply, I could understand that

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.

Thank you.

Regards

Kumar

Slider value based on multibox selection.

Miguel_Angel_Baeyens — Thu, 09 Sep 2010 15:55:05 GMT

Hi Kumar,

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.

Hope that helps

Slider value based on multibox selection.

Thu, 09 Sep 2010 16:15:06 GMT

Hi Miguel,

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

from

(SELECT trunc(ot.order_date) order_date, qcp.direct_channel, qscp.qc_flash_sp_type flash_category, count(ot.oid) placed_order_count

FROM orders_tab ot,

qc_cp_master qcp,

qc_report_scp_master qscp

WHERE TRUNC (ot.order_date) BETWEEN TO_DATE ('01/01/2010','MM/DD/YYYY') AND to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY')

AND ot.entity_oid = qcp.entity_oid

AND ot.order_status > 1

AND qscp.OID = ot.scp_oid

and qcp.direct_channel in ('Direct Marketing','Channel Marketing')

GROUP BY trunc(ot.order_date), qcp.direct_channel, qscp.qc_flash_sp_type) o,

(SELECT trunc(rt.create_date) inv_order_date, qcp.direct_channel,qscp.qc_flash_sp_type flash_category,

count(rt.order_oid) invoiced_order_count, sum(rt.inv_revenue) invoice_revenue

FROM revenue_tab rt,

qc_cp_master qcp,

qc_report_scp_master qscp

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 (+)

union

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

from

(SELECT trunc(ot.order_date) order_date, qcp.direct_channel, qscp.qc_flash_sp_type flash_category, count(ot.oid) placed_order_count

FROM orders_tab ot,

qc_cp_master qcp,

qc_report_scp_master qscp

WHERE TRUNC (ot.order_date) BETWEEN TO_DATE ('01/01/2010','MM/DD/YYYY') AND to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY')

AND ot.entity_oid = qcp.entity_oid

AND ot.order_status > 1

AND qscp.OID = ot.scp_oid

and qcp.direct_channel in ('Direct Marketing','Channel Marketing')

GROUP BY trunc(ot.order_date), qcp.direct_channel, qscp.qc_flash_sp_type) o,

(SELECT trunc(rt.create_date) inv_order_date, qcp.direct_channel,qscp.qc_flash_sp_type flash_category,

count(rt.order_oid) invoiced_order_count, sum(rt.inv_revenue) invoice_revenue

FROM revenue_tab rt,

qc_cp_master qcp,

qc_report_scp_master qscp

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

order by order_date;

Slider value based on multibox selection.

Miguel_Angel_Baeyens — Thu, 09 Sep 2010 17:09:36 GMT

Hello Kumar,

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.

Regards.