MTD Date Count to MTD in Straight Chart

[rammarthi]

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. 

[Vegar]

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]

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]

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

[Vegar · Accepted solution]

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]

Thank you, Vegar!! This works as Expected.

[qliksus]

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