topic Re: Set analysis for the sum of the current month customers in QlikView

Set analysis for the sum of the current month customers

Thu, 29 Jan 2015 01:39:53 GMT

I have a table like this:

Month	Product	Customers
1/1/2015	Product1	412
1/1/2015	Product2	17
12/1/2014	Product1	398
12/1/2014	Product2	15
11/1/2014	Product1	375
11/1/2014	Product2	16

I want to make a aggregation that calculates the number of all customers in the most recent month of selected data. Here's what I have so far:

sum({<Month = {$(=Max(Month))}>} Customers)

In this example, I would expect it to return 429 (412+17). This instead returns a null value, and I can't figure out why. Sorry if I missed something obvious.

Re: Set analysis for the sum of the current month customers

Thu, 29 Jan 2015 01:47:48 GMT

This seems to be a bigger problem. This also returns a null value:

=Max(Month)

My load script for Month says this:

MakeDate(if(fiscal_quarter<3,fiscal_year-1,fiscal_year),month_number, 01) as Month

(fiscal_quarter, fiscal_year, and month_number are all integers)

Re: Set analysis for the sum of the current month customers

PrashantSangle — Thu, 29 Jan 2015 04:02:58 GMT

Hi,

Try,

max(num(Month))

If possible share your sample app.

Regards

Re: Set analysis for the sum of the current month customers

crusader_ — Thu, 29 Jan 2015 05:11:41 GMT

Hi,

The key issue here that your field Month is not actually Month, it's a date.

And possibly it's a string, that's why your app can't find Max().

1. In text object try to convert explicitly your field Month into date format =Max(date(Month,'MM/DD/YYYY')) and check what it returns.

2. In set analysis Month and $(=max(Month)) should be exaclty the same. Check both in text objects.

Hope this helps.

Regards,

Andrei Kaliahin

Re: Set analysis for the sum of the current month customers

arulsettu — Thu, 29 Jan 2015 06:03:44 GMT

hi please find the attachment

Re: Set analysis for the sum of the current month customers

Thu, 29 Jan 2015 07:29:58 GMT

Hi Sophia,

Please find attached QVW. Hope it helps.

Thanks,

Chiru

Re: Set analysis for the sum of the current month customers

Thu, 29 Jan 2015 13:10:48 GMT

Try

sum({<Month = {$(=Max(Month(Month)))}>} Customers)

as expression.

Your field Month is a Date, not really Month.

Re: Set analysis for the sum of the current month customers

Thu, 29 Jan 2015 20:05:37 GMT

Thank you all for the help! I'm closer now, but it's still not working.

Andrei Kaliabhin and Alexander Ekimov:

You're both right, my "Month" is not a month. For the purposes of the rest of the data, I didn't want it to be a month, but rather the first date of the applicable month. I probably should have named that field something more appropriate, like month_start_date.

I changed the load script for "Month" to:

Date(MakeDate(if(fiscal_quarter<3,fiscal_year-1,fiscal_year),month_number, 01), 'M/D/YYYY') as Month

After doing that, a text obeject with =max(Month) is showing 1/1/2015, which is what I would expect. However, a text object with =$(=Max(Month)) is showing 0.00049627791563275. I would have expected either 1/1/2015 or 42005 (the numerical equivalent). A text object with $(=Max(date(Month))) also comes out with 0.00049627791563275.

max dreamer:

A text object with $(=Max(num(Month))) outputs 42005, which is an improvement. However, if I use the expression sum({<Month = {$(=Max(num(Month)))}>} Customers), that outputs 0. I think this is because it's comparing a date to a number. (However, I also tried changing the load script to load Month as a number, and that resulted in this expression outputting 0 as well.)

Arul Settu:

This works, but what about when February comes? I tested this QVW with February customer data and it sums all the customer counts for 2015. What I really need is just the sums of the latest month in 2015.

I tried changing the text object to =sum({<datenum={$(=max(datenum))}>}Customer), since that's closer to what I'm trying to do. However, that gave me null as the output.

chiranjeevi reddy:

I tried sum(if(Month(Month)=Month(now()),Customers)) in my application, but that returns an incorrect number. The number it produces in a chart also increases when I apply a dimension that shouldn't affect it. My other concern is that it might add up all the largest month numbers (i.e. December values) rather than the most recent month.

Re: Set analysis for the sum of the current month customers

Thu, 29 Jan 2015 21:29:14 GMT

SET ANALYSIS

makes a dashboard lot more meaningful and action oriented. For example, if you just provide number of units sold by a product line in a month, it is good information, but it is not-actionable. If you add comparison against same month last year, last month or average of relevant product lines in this month, you have added context to the number. The business user can take more meaningful actions out of this report / dashboard.

QlikView has feature called SET ANALYSIS that provides us a way to add this context. Set analysis predefines the SET OF DATA that our charts / tables use. So, using a Set Expression, we can tell our object (chart / table) to display values corresponding to various sets of data (e.g. a pre-defined time-period, geographic region, product lines etc.). All of the examples, I mentioned above as part of adding context can be accomplished using Set Analysis in Qlikview.

Most of the QlikView Professionals think that SET ANALYSIS is a complex feature. Through this post, I am trying to change their conviction towards it.

What is SET ANALYSIS ?

Set Analysis can be understood by a simple analogy of how Qlikview works. We make selections on certain variables and the changes reflect in the entire application. This happens because through our selection, we have created a set of data which we want to use. In a similar fashion, using Set Analysis feature, we can pre-define the data to be displayed in our charts.

Some features and characteristics for Set analysis are:

It is used to create different selection compared to the current application selections
Must be used in aggregation function (Sum, Count….).
Expression always begins and ends with curly brackets { }

Example dataset:

SET ANALYSIS syntax broken down into three components:

Identifiers:

Identifier	Description
0	:Represents an empty set, no records
1	:Represents the set of all the records in the application
$	:Represents the records of the current selection
$1	:Represents the previous selection
Bookrmark01	:Represents the set of all records against bookmark ID or the bookmark name

Examples:-

In below example, Current year selection is 2012 and previous selection was 2013.

Operators :

It works on set identifiers

Operator	Operator Name	Description
+	Union	Returns a set of records that belongs to union of sets.
-	Exclusion	Returns records that belong to the first but not the second
*	Intersection	Returns records that belong to both of the set identifiers.
/	Symmetric Difference	Returns a set that belongs to either, but not both of the set identifiers.

Examples:-

In below example, I have created a bookmark “BOOKMARK_1” for company selection A, B and C.

Modifiers:

Modifiers are always in angle brackets <>.
It consists multiple fields and all fields have selection criteria.
Condition of fields within modifiers bypass the current selection criteria.

Dollar Sign Expansion:

If we want to compare current year sale with previous year, previous year sales should reflect values in relation to current selection of year. For example if current selection of year is 2012, previous year should be 2011 and for current selection of year 2013, previous year is 2012.

“=Sum ({$<Year = {$ (=Max (Year)-1)} >} Sale) “

Above expression always returns sale for previous year. Here $ sign (Font color red) is used to evaluate the value for previous year. $ sign is used to evaluate expression and to use variables in set modifiers. If we have variable that holds last year value (vLASTYEAR) then expression can be written as:

“=Sum ({$vLASTYEAR)} >} Sale) “

Indirect SET ANALYSIS: Function P() and E()

Let us take a scenario, where we want to show current sales of the companies who had sales last year.

Expression should be similar like:

=sum({$<Year={$(=Max(Year))},Company_Name={Companies who had sales last year}> } Sale)

First we have to identify companies who had sales last year. To fix this problem, we will use function P() that is used to identify values within a field and function E() that exclude values within a field.

Finally, we have expression:

=sum({<Year={$(=Max(Year))},Company_Name=P({<Year={$(=Max(Year)-1)}>}Company_Name)>}Sale)

This post was an example where we have brought out methods to use SET ANALYSIS in Qlikview. Have you used this feature before? If yes, did you find it useful? Do you have more nifty tricks to make Set Analysis more interesting? If not, do you think this article will enable you to use Set Analysis in your next dashboard?

Do let me know your thoughts on using this feature in QlikView.

Re: Set analysis for the sum of the current month customers

Thu, 29 Jan 2015 21:31:37 GMT

go through it

Re: Set analysis for the sum of the current month customers

Thu, 29 Jan 2015 22:09:18 GMT

bommaramcharan,

Thank you for the additional context. I have read similar set analysis overviews, and they are always insightful. I like the power of set analysis, though I don't always get it right in practice. Are you suggesting that I use a specific part of this explaination in my solution?

I tried the test.qvw you attached, however it seems identical to the one uploaded by Arul Settu. Am I missing something, or did you mean to attach a different qvw file?

Re: Set analysis for the sum of the current month customers

PrashantSangle — Fri, 30 Jan 2015 03:46:10 GMT

Hi,

Then also use Date()

Try like,

sum({<Month = {$(=Date(Max(num(Month))))}>} Customers),

1st check in text object

=date(Max(num(Month))) //Format as per your month date format

Regards

Re: Set analysis for the sum of the current month customers

arulsettu — Fri, 30 Jan 2015 04:02:00 GMT

hi i found the solution

Re: Set analysis for the sum of the current month customers

Fri, 30 Jan 2015 05:04:58 GMT

Try creating 3 additional columns

Month(Month) as mon,
year(Month) as yr,
num(Month(Month)) as monthnum,

and use the following set analysis

=Sum({$ <yr={$(=max(yr))}, monthnum={$(=max(mon))} > } Customers)

-Qlikuser

Re: Set analysis for the sum of the current month customers

Fri, 30 Jan 2015 22:23:25 GMT

It's working! Thanks for all your help everyone!

I tried all the suggestions you sent in last night, but I couldn't get them to work. However, it gave me an idea that solved it.

For those who have a similar issue, here's what wound up working:

I added the following line to my load script.

num(year(Month))*100+num(month(Month)) as year_month_num

This gave me a number for each month like 201412, 201501, 201502, etc., with the most recent month having the highest number.

Then in my chart, I used the following expression.

Sum({<year_month_num = {$(=Max(year_month_num))}>} Customers)