Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

rammarthi
Contributor

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

Re: MTD Date Count to MTD in Straight Chart

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)
;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
6 Replies
Partner
Partner

Re: MTD Date Count to MTD in Straight Chart

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
...
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Partner
Partner

Re: MTD Date Count to MTD in Straight Chart

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...
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
rammarthi
Contributor

Re: MTD Date Count to MTD in Straight Chart

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

Partner
Partner

Re: MTD Date Count to MTD in Straight Chart

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)
;
Plees ekskuse my Swenglish and or Norweglish spelling misstakes
rammarthi
Contributor

Re: MTD Date Count to MTD in Straight Chart

Thank you, Vegar!! This works as Expected.

qliksus
Valued Contributor II

Re: MTD Date Count to MTD in Straight Chart

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