Qlik Sense Comparison to previous period Set analysis

[Anonymous]

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?

[sasiparupudi1]

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?

[Anonymous]

both are actually YYYY-MM-DD format. So its not an format issue I guess.

[sasiparupudi1]

Please post a sample

[jubarrosor]

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

[itec_pao]

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