topic Re: MTD Date Count to MTD in Straight Chart in QlikView

MTD Date Count to MTD in Straight Chart

rammarthi — Sat, 16 Nov 2024 04:00:18 GMT

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.

Re: MTD Date Count to MTD in Straight Chart

Vegar — Sun, 31 Mar 2019 08:25:30 GMT

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
...

Re: MTD Date Count to MTD in Straight Chart

Vegar — Sun, 31 Mar 2019 08:27:23 GMT

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/DateAndTimeFunctions/date-time-functions.htm

Re: MTD Date Count to MTD in Straight Chart

rammarthi — Mon, 01 Apr 2019 06:00:32 GMT

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

Re: MTD Date Count to MTD in Straight Chart

Vegar — Mon, 01 Apr 2019 08:07:18 GMT

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

Re: MTD Date Count to MTD in Straight Chart

rammarthi — Mon, 01 Apr 2019 09:41:10 GMT

Thank you, Vegar!! This works as Expected.

Re: MTD Date Count to MTD in Straight Chart

qliksus — Tue, 02 Apr 2019 12:18:53 GMT

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