Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
vipin_mishra479
Creator II
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
MarcoWedel

Hi,

one solution might be:

QlikCommunity_Thread_255201_Pic1.JPG

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

View solution in original post

12 Replies
vinieme12
Champion II
Champion II

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

Hi,

one solution might be:

QlikCommunity_Thread_255201_Pic1.JPG

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

View solution in original post

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
Creator II
Creator II

hello Vipin

see you in Example

Gruß gerry

vipin_mishra479
Creator II
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

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?