Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I need help completing an app as this is the last expression that I need to create but I'm not sure what steps I need to take.
I would like to count Active Members for my dimension Month. For example, see below color code. The End Date Month of 300001 is an indicator that the Member is still active.
An Active member is any member that falls within my Month dimension. I'm guessing I need to create an additioanl calendar to add my dimension but I'm not clear how to do this...
Script:
Table:
LOAD Date(MonthStart([Start Date Month], IterNo() - 1), 'YYYYMM') as Date,
Status,
Member,
[Start Date Month],
[End Date Month]
While MonthStart([Start Date Month], IterNo() - 1) <= If([End Date Month] >= MonthStart(Today()), MonthStart(Today()), [End Date Month]);
LOAD Status,
Member,
Date(MonthStart(Date#([Start Date Month], 'YYYYMM')), 'YYYYMM') as [Start Date Month],
Date(MonthStart(Date#([End Date Month], 'YYYYMM')), 'YYYYMM') as [End Date Month]
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
Straight Table:
Dimension:
Date
Expression:
=Count(DISTINCT Member)
Would you be able to provide a sample John?
I attached sample data. The Months should read as followed. The months are not in date format. I'm up for changing anything and everthing with the data to get the counts that I'm looking for. I found an example on QV community of kind of what I'm looking for.
Is this something you want on the front end or back end would also work?
I don't care as long as I can represent the data on a chart.
Script:
Table:
LOAD Date(MonthStart([Start Date Month], IterNo() - 1), 'YYYYMM') as Date,
Status,
Member,
[Start Date Month],
[End Date Month]
While MonthStart([Start Date Month], IterNo() - 1) <= If([End Date Month] >= MonthStart(Today()), MonthStart(Today()), [End Date Month]);
LOAD Status,
Member,
Date(MonthStart(Date#([Start Date Month], 'YYYYMM')), 'YYYYMM') as [Start Date Month],
Date(MonthStart(Date#([End Date Month], 'YYYYMM')), 'YYYYMM') as [End Date Month]
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
Straight Table:
Dimension:
Date
Expression:
=Count(DISTINCT Member)
I don't know what the script is doing but it's the results that I need. I'll review the script and try to understand the steps you took.
Thank you Sunny.