topic Re: How to use set expression to retrieve distinct users ever seen in QlikView

How to use set expression to retrieve distinct users ever seen

Mon, 23 Jun 2014 16:43:20 GMT

Consider the following data

Month Distinct Users Total Users

Jun-2014 42,115 72,000

May-2014 63,227 65,000
April-2014 59,126 61,000

Mar-2014 58,595 58,595

I have a text box that I'd like to display the current month Distinct Users by default. How would I use a set expression to display that?l

I'd also like to display a second text box which has the total distinct users ever seen based upon the month selected by the user. So I'd like to display 72,000 users by default because it is the current month, but have it adjust based upon the users selection on month,I

Example,

If the user were to select April-2014 text box 1 would display 59,126 active users and text box 2 would display the total number of users ever seen up intil April. (61,000). How would I use a set expression to count the distinct users ever up until April which is the users selection?

Re: How to use set expression to retrieve distinct users ever seen

maxgro — Mon, 23 Jun 2014 17:54:06 GMT

see attachment for some ideas

Re: How to use set expression to retrieve distinct users ever seen

Mon, 23 Jun 2014 18:41:58 GMT

unfortunately cannot open, possible to have you post here?

Re: Re: How to use set expression to retrieve distinct users ever seen

maxgro — Mon, 23 Jun 2014 18:50:45 GMT

script

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='jan;feb;mar;apr;may;jun;jul;aug;sep;oct;nov;dec'; // !!!

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

table:

load

date(Date#(Month, 'MMM-YYYY')) as Date,

Date#(Month, 'MMM-YYYY') as Month,

[Distinct Users] ,

[Total Users];

// Months in Month field are the same as SET MonthNames

load * inline [

Month , Distinct Users , Total Users

Jun-2014 , 42115 , 72000

May-2014 , 63227 , 65000

Apr-2014 , 59126 , 61000

Mar-2014 , 58595 , 58595

];

text box (from top)

current or selected

=if(GetSelectedCount(Month)=1,

sum( [Distinct Users]),

sum({$ <Month={"$(=date(today(),'MMM-YYYY'))"}>} [Distinct Users])

)

current month

=sum({$ <Month={"$(=date(today(),'MMM-YYYY'))"}>} [Distinct Users])

selected (only if one month selected)

=sum({$ <Month={"$(=only(Month))"}>} [Distinct Users])