Counting Number of Days in Current Quarter

[MattNatz]

Good afternoon Qlik Friends!

I am struggling on trying to create the formula on counting the amount of days that have passed in the current quarter.  For example, I want it to show me how many days have full passed for Q2 as of today no matter the day of the month.  

Below are the TempCalendars that have been setup in the LoadEditor:

//Calendar for Sales Dates
QuartersMap:
MAPPING LOAD
rowno() AS Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);

Temp:
Load
min (Saledate) AS minDate,
max (Saledate) AS maxDate
Resident Salesdata;

Let vSaleMinDate = Num(Peek('minDate', 0, 'Temp'));
Let vSaleMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
$(vSaleMinDate) + Iterno() -1 As Num,
Date($(vSaleMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(vSaleMinDate) + IterNo() -1 <= $(vSaleMaxDate);

SaleDateCalendar:
Load
TempDate AS Saledate,
Year(TempDate) As SaleYear,
YeartoDate(TempDate)*-1 as SaleCurYTDFlag,
YeartoDate(TempDate,-1)*-1 as SaleLastYTDFlag,
ApplyMap('QuartersMap', month(TempDate), Null()) & '-' & Year(TempDate) as SaleQuarterYear,
Month(TempDate) & '-' & Year(TempDate) AS SaleMonthYear,
If(TempDate > Date(MonthStart('$(vSaleMaxDate)', -12)) and TempDate <= Date('$(vSaleMaxDate)'-1), 'Last 12 Months','null') as Last12Months
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;

Any help on this would be greatly appreciated as I am new to Qlik and trying to take over for someone that had to leave the company quickly.

Matt

[_ylc]

You can first create a variable that determines what is the current quarter today

vCurrentQtr = MaxString({<Saledate={"=$(=Today())"}>}SaleQuarterYear)

Then you can now create the formula that counts the number of days

=count(distinct {<SaleQuarterYear={"$(vCurrentQtr)"}, Saledate={"<=$(=Today())"}>} Saledate)

This one will count all the Saledate this Quarter and the date is less than or equal today.

The result will give you 71 days.

[MattNatz]

Thank you very much this worked perfectly!

[MattNatz]

Would there be any reason why applying section access seems to break this formula?  Works perfectly outside with no section access applied, once section access is applied it drops the number of days to single digits.  Could this be because we only load monthly aggregate data?  For example, we use MTD and Total Month loads only?  If so, then why would this not apply outside section access when the same data is loaded?

[_ylc]

It's possible that section access affects this formula.

Let's say User1 = have all the data available, User2 = however only to get see data for 5 transactions, meaning only 5 dates are available for User2. You can create an island table of the complete dates, instead of counting dates on your transaction table, you will count the dates on the island calendar table.