topic Re: Date Range Help in QlikView

Date Range Help

lockematthewp — Tue, 05 Mar 2019 20:23:54 GMT

I currently have a bar chart showing fuel usage for certain dates. I would instead like the X axis to show a date range. So instead of the label '2/21/19' I would like it to show '2/19/19 to 2/21/19' (if the previous date was 2/18/19). I would appreciate it if anyone can help me.

Thanks,

Matt

Re: Date Range Help

crusader_ — Tue, 05 Mar 2019 22:28:24 GMT

Hi Matt,

Just to make sure I understood requirements: if the value (price) remains the same during some period you'd like to see a date range (from-to) instead of individual date.

If so, then this functionality is not available by default, so to achieve desired result you need to create a separate dimension field in a form you would like to see it.

If you'd like to group your dates and show sum(Values) within that period than try class() function in conjunction with replace (to replace '<=x<' to '-')

Hope this helps

//Andrei

Re: Date Range Help

lockematthewp — Wed, 06 Mar 2019 13:30:44 GMT

It's not so much that the price remains the same. It is that a fuel reading is only read every few days. So the customer would like to see how much fuel was used for those few days since the last reading. For example:

1/5 50 gallons

1/9 20 gallons

1/14 70 gallons

So it should say:

(X axis) (Y axis)

1/1 to 1/5 50 gallons

1/6 to 1/9 20 gallons

1/10 to 1/14 70 gallons

Re: Date Range Help

crusader_ — Wed, 06 Mar 2019 23:08:09 GMT

Unfortunately Qlik is not very good at "showing things which doesn't exist", so to get to the point you'd need to do some scripting...

Please find attached example how it might be done (check script comments).

Hope this helps.

//Andrei

Re: Date Range Help

lockematthewp — Thu, 07 Mar 2019 15:54:20 GMT

Thank you Andrei. Your example works exactly how I wanted. I am having a little trouble getting it to work with my data however. Are you able to show me an example with multiple excel files loaded instead of inline?

Re: Date Range Help

crusader_ — Thu, 07 Mar 2019 23:00:56 GMT

Hi,

Please find attached example.

Hope this helps.

//Andrei