Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
rammarthi
Creator
Creator

MTD Date Count to MTD in Straight Chart

I have a dimension and a date column. Now starting every month I wanted count of days till latest date (count of dates in MTD format) in a column.

A sample file is attached for reference. 

1 Solution

Accepted Solutions
Vegar
MVP
MVP

I also notice that you have diffrent definition of work days per Name. You can solve this issue with the following script. It is important that the DeteOfOrder is sorted in date order per Name. 

LOAD 
    Name, 
    Date(DateOfOrder) as DateOfOrder,
    autonumber(Name&DateOfOrder, hash256(Name, MonthName(DateOfOrder))) as  DaysCountToMTD
FROM
    "MTDdateCount.xlsx"
     (ooxml, embedded labels, header is 1 lines, table is Sheet1)
;

View solution in original post

6 Replies
Vegar
MVP
MVP

Did you apply some special logic to your calculation, hence 1/7/2018 gets value 5?

Without any special logic you should get what you are looking for by using the Day() function.

Load
Date,
Day(Date) as DayInMonth
...
Vegar
MVP
MVP

You will find an overview of all available date and time functions in the Qlik help page: https://help.qlik.com/en-US/qlikview/November2018/Subsystems/Client/Content/QV_QlikView/Scripting/Da...
rammarthi
Creator
Creator
Author

Yes we have taken only working days into account. Assume all the missing dates are holidays.

Vegar
MVP
MVP

I also notice that you have diffrent definition of work days per Name. You can solve this issue with the following script. It is important that the DeteOfOrder is sorted in date order per Name. 

LOAD 
    Name, 
    Date(DateOfOrder) as DateOfOrder,
    autonumber(Name&DateOfOrder, hash256(Name, MonthName(DateOfOrder))) as  DaysCountToMTD
FROM
    "MTDdateCount.xlsx"
     (ooxml, embedded labels, header is 1 lines, table is Sheet1)
;
rammarthi
Creator
Creator
Author

Thank you, Vegar!! This works as Expected.

qliksus
Specialist II
Specialist II

Using a Rowno() function in straight table will give the same result

Dimesion : Name , Month( Hide this column) , Date

Expression : Rowno()

Just sort the table by Name