# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Did you mean:
Creator II

## Need to Count Number of Days

Hi All,

here i have attached the excel file where i make a mokeup data

In the attached excel i have four cloumn

Year   Month   Day   Counter

against each day i have counter as 0 and 1 where 1 is working days and 0 means non working days. Now based on excell data i want calculate no of days for each month and year

the out put result i have mention in excell please look and provided me a feed back.

1 Solution

Accepted Solutions
MVP

Hi,

one solution might be:

Month,

Day,

Counter,

If(Counter,AutoNumber(RecNo(),Year&Month),0) as Output

hope this helps

regards

Marco

12 Replies
Champion II

Try as below

FACT:

Month,

Day,

Counter,

if(Counter=0,0,OP1) as Output ;

Month,

Day,

Counter,

if(Year=Peek(Year) and Month = Peek(Month),rangesum(Peek(OP1),Counter),Counter) as OP1

FROM

Fullpath\Qlikview Test.xlsx

(ooxml, embedded labels, table is Sheet1);

MVP

Hi,

one solution might be:

Month,

Day,

Counter,

If(Counter,AutoNumber(RecNo(),Year&Month),0) as Output

hope this helps

regards

Marco

MVP

Try this

Table:

Month,

Day,

Counter

FROM

(ooxml, embedded labels, table is Sheet1);

FinalTable:

If(Year = Previous(Year) and Month = Previous(Month), RangeSum(Peek('Output'), Counter), Counter) as Output

Resident Table

Order By Year, Month, Day;

DROP Table Table;

MVP

Or this

Table:

Month,

Day,

Counter

FROM

(ooxml, embedded labels, table is Sheet1);

FinalTable:

If(Counter = 1, Output, 0) as FinalOutput;

If(Year = Previous(Year) and Month = Previous(Month), RangeSum(Peek('Output'), Counter), Counter) as Output

Resident Table

Order By Year, Month, Day;

DROP Table Table;

MVP

Wouldn't we need to sort the data using a resident load in case the data is not sorted by Year, Month, Day or by Date in ascending order? Or will this still work?

Creator II

hello Vipin

see you in Example

Gruß gerry

Creator II
Author

Hi Marco,

I have a 1 question if i have region for all day then how i can apply this

e.g - East - 1

West - 1

North - 1

South - 1

East - 2

West - 2

North - 2

South - 2

In Excel i forget 1 column which is region

MVP

Yes, you are right.

This solution only works for already sorted source tables.

regards

Marco

MVP

Great, also, did you just load from an Excel file attached to this post? Is that something we can do from QlikView?