Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have hard time getting my bar chart working as I would like to. What I would like, is to have a previous period from my selections next to the bar of current selections.
The formula i have now:
Count({<[Document Timestamp.autoCalendar.InYTD]=,[Document Timestamp.autoCalendar.Month]=,
[Document Timestamp.autoCalendar.Date]={">=$(vMinDateLastPeriod)<=$(vMaxDateLastPeriod)"}>}DISTINCT Key_SessionCounter)
I have put my variables to their own KPI boxes and they work fine.
Could someone explain why my formula is not working?
Yes, most likely it is the issue.. LHS and RHS dates must be identical format
What format is [Document Timestamp.autoCalendar.Date]
and what do you see if you put $(vMinDateLastPeriod) in a text box?
both are actually YYYY-MM-DD format. So its not an format issue I guess.
Please post a sample
Hi:
Sometimes I had problems with comparison to previous period Set analysis when I used dates.
I usually use a calculatd field named Period_id to order months in the calendar.
For example:
CALENDAR_TMP:
LOAD
DISTINCT
year(date(RecNo()+$(Date)-1,'DD/MM/YYYY')) as "Year",
month(date(RecNo()+$(Date)-1,'DD/MM/YYYY')) as "Month",
Num(month(date(RecNo()+$(Date)-1,'DD/MM/YYYY')),'00') as "NumericMonth",
capitalize((month(date(RecNo()+$(Date)-1,'DD/MM/YYYY')))&' '&Right(year(date(RecNo()+$(Date)-1,'DD/MM/YYYY')),2)) as "MonthYear",
year(date(RecNo()+$(Date)-1,'DD/MM/YYYY')) & Num(month(date(RecNo()+$(Date)-1,'DD/MM/YYYY')),'00') as "YearMonth"
AUTOGENERATE AddMonths($(sisFechaFin)-$(Date),0);
CALENDAR:
LOAD
*,
RowNo() as Period_id
Resident CALENDAR_TMP
ORDER BY Año, NumMes;
Drop table CALENDAR_TMP;
Now, I can use this field in set analysis:
set vMaxDate = max({<Period_id=max(Period_id)>} Date);
set vMaxDatePrev = max({<Period_id=max(Period_id-1)>} Date);
Best Regards,
Juan P. Barroso
Hi all,
When working with Date check on the format. Because the data type returned can change depending on different expression. For example:
today() --> Date
today()-1 --> Number
only(InvoiceDate) --> Date
max(InvoiceDate) --> Number
Addmonths(....) --> Date
Today()+14 --> Number
Therefore you may try to use the function date() for this kind of use in set analysis:
Date = {">Date(today() + 12)"}
Regards,
Oktay