topic Re: stuck with a bar chart... in QlikView

stuck with a bar chart...

Fri, 28 Aug 2015 13:17:09 GMT

Hello community, i need your help

I am working on a bar chart to show the number of events running during a period

the actual bar chart is only counting the event "once"

Lets say "Les mains sales" showing from 23.04 to the 08.05 would actually be visible only the 23 of april which is Thursday (jeudi) but will not count for Friday, saturday....etc....until the 08.05.

The person is charge of the agenda would like to be able to say, tonight we have "X" number of events running ...but in this case, if an event is running from monday to friday...we should count it everyday.

hope that i am clear...how would you handle this.

Best

Re: stuck with a bar chart...

swuehl — Fri, 28 Aug 2015 13:31:06 GMT

You can solve this by using either IntervalMatch or

Creating Reference Dates for Intervals

Re: stuck with a bar chart...

Fri, 28 Aug 2015 13:54:02 GMT

great input. i had a look at it and this exactly what i need...however i do not know exactly how to implement it...could you please help me ? this is my current Script.

SET ThousandSep="'";
SET DecimalSep='.';
SET MoneyThousandSep="'";
SET MoneyDecimalSep='.';
SET MoneyFormat='fr. #''##0.00;fr.-#''##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

LOAD title,
types,
Name_company,
if(match(Name_company,'Fnac Rive','Fnac Balexert','Billeterie fnac'), 'Regroup',Name_company) as Fnac,
manif_in,
Year(manif_in) as Year,
Month(manif_in) as Month,
dual(upper(left(Month(manif_in),1)),num(Month(manif_in))) as Abrev_Month,
Dayname (manif_in) as Day,
WeekDay (manif_in) as weekday,
manif_out,
action_company.ActiveL,
company.Key,
manif.K_ACTION,
Profiles.Name,
Profiles.K_PROFILE
FROM

(biff, embedded labels);

Re: stuck with a bar chart...

swuehl — Fri, 28 Aug 2015 17:24:58 GMT

If title is a key field and your manif_in and manif_out are correctly interpreted as date values by QV (adjust the standard date format if not or use date#() to interpret the values), you can try something along these lines (implementing the second approach):

SET ThousandSep="'";
SET DecimalSep='.';
SET MoneyThousandSep="'";
SET MoneyDecimalSep='.';
SET MoneyFormat='fr. #''##0.00;fr.-#''##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

INPUT:
LOAD title,
types,
Name_company,
if(match(Name_company,'Fnac Rive','Fnac Balexert','Billeterie fnac'), 'Regroup',Name_company) as Fnac,
manif_in,
//Year(manif_in) as Year,
//Month(manif_in) as Month,
//dual(upper(left(Month(manif_in),1)),num(Month(manif_in))) as Abrev_Month,
//Dayname (manif_in) as Day,
//WeekDay (manif_in) as weekday,
manif_out,
action_company.ActiveL,
company.Key,
manif.K_ACTION,
Profiles.Name,
Profiles.K_PROFILE
FROM

(biff, embedded labels);

REFDATES:

LOAD

title,

Date( manif_in +IterNo() -1) as ReferenceDate

RESIDENT INPUT

WHILE IterNo() <= manif_out -manif_in +1;

Calendar:

Load Date as ReferenceDate, Month(Date) as Month, Year(Date) as Year, Dayname(Date) as Date, Weekday(Date) as weekday;

Load Date(MinDate+iterno()) as Date While iterno() <= MaxDate - MinDate ;

Load Min(ReferenceDate)-1 as MinDate, Max(ReferenceDate) as MaxDate resident REFDATES;

edit: Date as ReferenceDate in Calendar