Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Number of days in a month

Hello,

I am trying to calculate the no of days in a particular month, and in my case it is not the normal calendar month rather a Fiscal month.

Details are attached here.

   

Fisc PeriodSales end of Fisc periodNo of Fiscal days
2016058909.1928
2016064938.335
2016071099928
201608548728
2016091187835
20161054898

in the above table, the last column is based on the Fisc_day_of_month column in the Calendar sheet.

Please let  me know if more information is needed.

Tags (1)
11 Replies
Highlighted

Re: Number of days in a month

I am confused, is the above table given or are we looking to calculate No of Fiscal Days field in the script?

Highlighted
Not applicable

Re: Number of days in a month

Hello Sunny,

We have the column "Fisc_day_of_month" in the Calendar, so all we have to do is to get the Max of every Fiscal_Period. And if we are in the middle of the month, like today Oct 11th, we will have to get the date from the Sales tab of the excel and get the corresponding Fisc_Day_of_month number.

you will get a better picture if you take a look at the different tabs of the attachment.

Highlighted

Re: Number of days in a month

May be something like this:

Day(Max(Fisc_day_of_month))

Highlighted
Not applicable

Re: Number of days in a month

No Sunny, it would not give me the right output. In the excel attachment, i have the calendar which has the Fisc Dayof Month numbers for our regular calendar dates. And i just want to pick the highest number for every Fiscal Period. And only for October, we are on 11th Oct, but the most recent entry for Oct is on 9th Oct in the Sales tab, so we will have to pick the Fisc number of 9th Oct.

Highlighted

Re: Number of days in a month

Something like this?

Table:

LOAD Date,

    Fisc_day_of_month,

    Fisc_Period

FROM

[Max date.xlsx]

(ooxml, embedded labels, table is Calendar);

Left Join (Table)

LOAD Fisc_Period,

  Max(Fisc_day_of_month) as [No of Fiscal days]

Resident Table

Where Date < Today()

Group By Fisc_Period;

Sales:

LOAD Date,

    Sales,

    Ctry

FROM

[Max date.xlsx]

(ooxml, embedded labels, table is Sales);

Capture.PNG

Highlighted
Not applicable

Re: Number of days in a month

Thanks for the suggestion Sunny.

However the calendar table is joined already with adifferent field to my fact tables. Is there a way to achieve this at the front end?

Highlighted

Re: Number of days in a month

Using this expression:

=Max({<Date = {"$(='<' & Date(Today()))"}>}Fisc_day_of_month)


Capture.PNG

Highlighted
Not applicable

Re: Number of days in a month

Thanks for the suggestions Sunny, It seems to work when we display it against the Fisc Period, but it does not work when i use along with different expressions. e.g. FirstSortedValue function is used in the attached sample, to get the latest Sales of every Fisc Period and then it should be divided by the No of days in that Fisc Month. Please take a look at the attached sample.

Highlighted

Re: Number of days in a month

Does this look right?

Capture.PNG

I changed your expression to this

=Max({1<Date = {"$(='<' & Date(Today()))"}>}Fisc_day_of_month)

Now the no of days won't change based on any selection you make. Does this look what you were looking for? Or was there another issue?