Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Sort chart on weekday untill today

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)3228
Saterday (08-03-2014)6055
Sunday (09-03-2014)5954
Monday (10-03-2014)2822
Tuesday (11-03-2014 (before last))3126
Wednesday (12-03-2014 (yesterday))2923
Thursday (13-03-2014 (today))3328

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?

1 Solution

Accepted Solutions
Not applicable

Re: Sort chart on weekday untill today

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

3 Replies
mov
Esteemed Contributor III

Re: Sort chart on weekday untill today

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

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Sort chart on weekday untill today

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

Community Browser