Need to Count Number of Days

[vipin_mishra479]

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.

[vinieme12]

Try as below

FACT:

Load Year,

     Month,

     Day,

     Counter,

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

LOAD Year,

     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);

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

LOAD Year,
    Month,
    Day,
    Counter,
    If(Counter,AutoNumber(RecNo(),Year&Month),0) as Output
FROM [https://community.qlik.com/servlet/JiveServlet/download/1240783-272119/Qlikview%20Test.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

[sunny_talwar]

Try this

Table:

LOAD Year,

    Month,

    Day,

    Counter

FROM

[..\..\Downloads\Qlikview Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

  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;

[sunny_talwar]

Or this

Table:

LOAD Year,

    Month,

    Day,

    Counter

FROM

[..\..\Downloads\Qlikview Test.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

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

LOAD *,

  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;

[sunny_talwar]

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?

[gerry_hdm]

hello Vipin

see you in Example

Gruß gerry

[vipin_mishra479]

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

[MarcoWedel]

Yes, you are right.

This solution only works for already sorted source tables.

regards

Marco

[sunny_talwar]

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