topic Re: Assistance in set analysis in QlikView

Assistance in set analysis

david_ze — Thu, 21 Jun 2012 09:44:07 GMT

Hi All,

I'd like to show all sales for the current year, with the exception of the selected month (if not selected than the current month will be the exception),

fields are:

Year, Month, Total_Sales

I'll appreciate you assistance on this,

Thanks,

David

Re: Assistance in set analysis

deepakk — Thu, 21 Jun 2012 09:54:05 GMT

Hi David,

I hope this what you are looking for.

Sum({<Year={$(=Max(Year))},Month=>} Total_Sales)

Deepak

Re: Assistance in set analysis

Miguel_Angel_Baeyens — Thu, 21 Jun 2012 11:06:44 GMT

Hi David,

Assuming you have a Date field based on Year and Month, the result should be a range in set analysis:

Sum({< Date = {">=$(=YearStart(Today()))<=$(=MonthEnd(AddMonths(Today(), -1)))"} >} Sales)

That builds a range from the first day of the current year to the last day of the previous month.

It's even easier to create a flag field in the script so if Year equals to Year(Today()) and the date is less than or equal to the previous month date the value is set to 1, otherwise is set to 0. Then the expression is simplified to

Sum({< IsCurrent = {1} >} Sales)

As a general rule and best practice, do this sort of flag fields as often as possible, in order to simplify expressions and get better performance.

Hope that helps.

Miguel

Re: Assistance in set analysis

david_ze — Fri, 22 Jun 2012 21:56:29 GMT

hi Miguel,

Thanks for the helpful answer.
Can you advise on what should I change in the this set analysis if I would like to let to user choose the base month?
For example, if the user will choose sale month May, than I should present the sales in the current year from Jenuary till April (without May).
Field are: Sale_Month, Sale_Date.
Thanks again,

David

Re: Assistance in set analysis

Miguel_Angel_Baeyens — Mon, 25 Jun 2012 07:11:32 GMT

Hi David,

I use to do that using a variable instead of a field. For example, you create a new Calendar object, using a variable vDateSelected instead of a field, that lets the user select any given month and populate it (or you can use an inputbox as well).

So in your expressions, the Date field is always depending on the selection by the user:

Sum({< Date = {'>=$(=YearStart(vDateSelected))'} >} Sales)

You can find more on set analysis and variables in this application.

Hope that helps.

Miguel