Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I want to show interest days in a table by month in qlik Sense. In january it should be interest/31 days, february/30 days etc. How do I calculate this best in the sqript?
I have tried if(month={jan,mar,may....31,
if(month={apr,jun,sept....30,
29))
But I only get the correct calculation for either months with 30 or 31..
If you have a date field;
in your script; do as this to create a day field and a month field (if it's not already created):
Day([Calendar date]) as [Day of date]
,Month([Calendar date]) as [Month of date]
,date(MonthStart([Calendar date]),'MMM YYYY') as [Year month date]
Now, in your table, you'll have as dimension your [Month of date]field or [Year month date] (if you want to show month and year: depends on the year, Feb could have 28 or 29 days) and as a measure: count(distinct [Day of date])
result:
*february/28 days
If you have a date field;
in your script; do as this to create a day field and a month field (if it's not already created):
Day([Calendar date]) as [Day of date]
,Month([Calendar date]) as [Month of date]
,date(MonthStart([Calendar date]),'MMM YYYY') as [Year month date]
Now, in your table, you'll have as dimension your [Month of date]field or [Year month date] (if you want to show month and year: depends on the year, Feb could have 28 or 29 days) and as a measure: count(distinct [Day of date])
result:
Thanks!