Set Analysis show the number from a month ago

[WildmoserGeorg]

Hello,

I would like to display the value from exactly one month ago in a table (dimension days).  Example: 3 logs were counted on 01/01/2023. These should be displayed again in a separate column as a value from a month ago on 02/01/2023.

What am i doing wrong in the Set Analysis? Where is my mistake in reasoning?

Thanks 4 help. 🙂

These are the set analysis used:

=count(logid)

=addmonths(date,-1)

=count({<date = {"$(=addmonths(date,-1))"}>}logid)

The datamodel:

Here the Script:

Logs:
load * inline [
logid,date
1,01.01.2023
2,01.01.2023
3,01.01.2023
4,01.02.2023
5,01.02.2023
6,02.02.2023
7,01.02.2023
8,01.02.2023
9,02.02.2023
10,02.02.2023
11,02.02.2023
]
;

tmp_minmax:
load
num(min(date(date))) as minDatum,
num(max(date(date))) as maxDatum
resident Logs;

LET varMinDate = Num(peek('minDatum',0,'tmp_minmax'));
LET varMaxDate = Num(peek('maxDatum',0,'tmp_minmax'));

drop table tmp_minmax;

// Creating a Temporary Calendar

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

// Date Dimension

Calendar:
Load

date(TempDate) as date,
Day(date(TempDate)) as Tag,
Month(date(TempDate)) as Monat,
Year(date(TempDate)) as Jahr
Resident TempCalendar
Order By TempDate ASC;

Drop Table TempCalendar;

[vinieme12 · Accepted solution]

Create an AsOf Table to associate prior month date data and differentiate using a flag field called "DateType" in below example

Logs:
load logid,date(date#(date,'DD.MM.YYYY')) as date inline [
logid,date
1,01.01.2023
2,01.01.2023
3,01.01.2023
4,01.02.2023
5,01.02.2023
6,02.02.2023
7,01.02.2023
8,01.02.2023
9,02.02.2023
10,02.02.2023
11,02.02.2023
];

AsOfRel:
Load Distinct date as AsOfDate ,'SameDay' as Datetype, date as date
Resident Logs;
Concatenate(AsOfRel)
Load Distinct date as AsOfDate ,'1MonthPrior' as Datetype, addmonths(date,-1) as date
Resident Logs;

tmp_minmax:
load
num(min(date(date))) as minDatum,
num(max(date(date))) as maxDatum
resident Logs;

LET varMinDate = Num(peek('minDatum',0,'tmp_minmax'));
LET varMaxDate = Num(peek('maxDatum',0,'tmp_minmax'));

drop table tmp_minmax;

// Creating a Temporary Calendar

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

// Date Dimension

Calendar:
Load

date(TempDate) as date,
Day(date(TempDate)) as Tag,
Month(date(TempDate)) as Monat,
Year(date(TempDate)) as Jahr
Resident TempCalendar
Order By TempDate ASC;

Drop Table TempCalendar;

In Charts:

Use Dimension = AsOfDate

Measure1 =  Count({<Datetype={'SameDay'}>}logid)

Measure1 =  Count({<Datetype={'1MonthPrior'}>}logid)

[MayilVahanan · Accepted solution]

Hi 

You can use "As of" Calendar

Logs:
load * inline [
logid,date
1,01.01.2023
2,01.01.2023
3,01.01.2023
4,01.02.2023
5,01.02.2023
6,02.02.2023
7,01.02.2023
8,01.02.2023
9,02.02.2023
10,02.02.2023
11,02.02.2023
12,02.01.2023
13,02.01.2023
]
;

tmp_minmax:
load
num(min(date(date))) as minDatum,
num(max(date(date))) as maxDatum
resident Logs;

LET varMinDate = Num(peek('minDatum',0,'tmp_minmax'));
LET varMaxDate = Num(peek('maxDatum',0,'tmp_minmax'));

drop table tmp_minmax;

// Creating a Temporary Calendar

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

// Date Dimension

Calendar:
Load

date(TempDate) as date,
Day(date(TempDate)) as Tag,
Month(date(TempDate)) as Monat,
Year(date(TempDate)) as Jahr
Resident TempCalendar
Order By TempDate ASC;

Drop Table TempCalendar;

// 1. Create a distinct list of Months:
date:
Load distinct
date
Resident
Calendar
;


// 2. Create a distinct list of DisplayMonths:
Displaydates:
Load
date as Displaydate
Resident
Calendar
;

// 3. Create a list of all possible combinations:
join (date) load * resident Displaydates;

// 4. Reload the same table and calculate all the necessary flags:
MonthsLink:
Load
date,
Displaydate,
IF( date = AddMonths(Displaydate,-1) , 1, 0) as Last1MonthsFlag,
IF( date = Displaydate , 1, 0) as SameMonthFlag
Resident
date
;
drop table date, Displaydates;

Front End:

Dim: Displaydate

Exp1: Count({<SameMonthFlag={1}>}Distinct logid)

Exp2: Count({<Last1MonthsFlag={1}>}Distinct logid)

Its one of the method to achieve. 

[WildmoserGeorg]

Thanks @vinieme12 and @MayilVahanan 👍

The solution works fine.