topic Re: Sort chart on weekday untill today in QlikView

Sort chart on weekday untill today

Tue, 12 May 2015 12:12:37 GMT

Hello all,

I have a problem with one of my charts. I have been looking for hours but can't find what i need (some variations, but nothing that really works).

I want a chart with weekday as dimension and 2 expressions.

the first expression is the amount of orders I had in the last 7 days per day. I do this with "count({<Date = {">=$(=Date('13-03-2014'-6))<=$(=Date(Date('13-03-2014')))"}>} OrderID)" (I have to work with an old database for creation. In this "13-03-2014" is the last entry so I take that as today)

The second expression is the average orders on those days in the selected period (for instance if I select 2013, it will show the average orders over all of 2013 per day.

In a table this should be something like this:

Date	Expression 1 (amount of orders last 7 days) (For example from 07-03 untill 13-03)	Expression 2 (average amount of orders in selected periode) (For example the entire year 2013)
Friday (07-03-2014)	32	28
Saterday (08-03-2014)	60	55
Sunday (09-03-2014)	59	54
Monday (10-03-2014)	28	22
Tuesday (11-03-2014 (before last))	31	26
Wednesday (12-03-2014 (yesterday))	29	23
Thursday (13-03-2014 (today))	33	28

The problem is that both expressions only show the data from that week or both expressions show data from everything I have selected. But not Expression 1 only from last week and expression 2 from the selected period.

Can anyone help me please?

Re: Sort chart on weekday untill today

Anonymous — Tue, 12 May 2015 12:38:58 GMT

Try using this for the 1st column:
count(distinct if(Date>=ReloadTime()-7, OrderID))
2nd column:
avg(OrderID)

If I got the request wrong - could you please upload the app?

Re: Sort chart on weekday untill today

Gysbert_Wassenaar — Tue, 12 May 2015 12:43:58 GMT

I want a chart with weekday as dimension and 2 expressions

Did you create a Weekday field in the script? Or is that chart dimension a date field? I suspect the latter. If so, you try to create a WeekDay field using the WeekDay function.

You may want to map the weekday numbers to names with a small inline mapping table and the applymap function.

Re: Sort chart on weekday untill today

Mon, 18 May 2015 09:54:36 GMT

Thanks for the help! With some minor changes it worked well! Thanks again!

Expression 1:

= count({1}DISTINCT if(Date>=Date('13-03-2014')-6 and Date<=Date('13-03-2014'), OrderID))

Expression 2:

=num(count(OrderID) / count( distinct if(OrderID>0, weeknumber & YYYY)),'#.###,##')

Dimension:
= Weekday