Number of count dates

[Anonymous]

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?

[swuehl]

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

[rubenmarin]

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: