topic Number of count dates in QlikView

Number of count dates

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a chart that has store numbers and the days of the week that will either be blank or have an X in them. When there is a X in them that means that is a day they count.

EX)

STORE	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY
1	X		X		X
2		X		X
3	X			X
4	X	X	X	X	X
5	X	X
6	X				X
7					X
8		X
9			X	X
10		X		X

I am trying to find a way to sum up the number of count days they would have for a given month. I do have another table that has dates in it. I am not sure if I would need more data or what I would need to do to get this. Any ideas?

Re: Number of count dates

swuehl — Tue, 08 Mar 2016 22:13:39 GMT

How does your source data or the data in your data model looks like? Could you post some sample table records?

Re: Number of count dates

rubenmarin — Tue, 08 Mar 2016 23:00:12 GMT

Hi, please check the attached example.

MAP_DayNumbers: //Day number depends on regional settings, in mine, 0=Monday

Mapping LOAD * Inline [

Day, Number

MONDAY, 0

TUESDAY, 1

WEDNESDAY, 2

THURSDAY, 3

FRIDAY, 4

];

DataOrig: //Your data loaded as a Crosstable to check field names

CrossTable(DayOfWeek, Check)

LOAD STORE,

MONDAY,

TUESDAY,

WEDNESDAY,

THURSDAY,

FRIDAY

FROM

[.\test.xlsx]

(ooxml, embedded labels, table is Hoja1);

Data:

LOAd *, ApplyMap('MAP_DayNumbers', DayOfWeek, null()) as WeekDay, If(Check='X', 1, 0) as daysCount Resident DataOrig;

Left Join (Data)

LOAD Date,

Month(Date) as Month,

WeekDay(Date) as WeekDay;

LOAD Date(MinDate + IterNo()-1) as Date

While MinDate + IterNo()<MaxDate;

LOAD Date('01/01/2016') as MinDate,

Date('31/12/2016') as MaxDate

AutoGenerate 1;

DROP Table DataOrig;

Result for march: