topic Re: Set analysis with / without current selections in Water Cooler

Set analysis with / without current selections

breukelaarj — Fri, 20 Jun 2014 07:29:40 GMT

Hi,

I hope someone can tell me if there is a chance I can get my idea to work.

Assume a table with 'ordernumber' and 'orderdate'.

From the 'orderdate' a master calendar is created.

So a simple calculation would be to count the 'ordernumber'. This way I can view the number of orders placed per timeframe.

Assume I want selections on Year and Month. My Master calendar provides this. So I can view the number of orders per month, year combination.

If I would also show in my table the number of orders of previous month I can do so by using a function in my expression for current date and calculate back a month. But that is sort of a static view in time, current month versus previous month.

What I want is current selected period versus previous selected period.

To start simple assume I have to select a month. So if I select March as month my next column would need to show the count of February. I need to use set analysis (assumption) and I need to exclude the month selection from the expression, but how can I use it anyhow to calculate the previous month? I have tried various examples but to no avail.

I would be thrilled to have a working solution for above. I would be more thrilled if there was a way to determine the selected period. For example I only select year that it would show the count of the previous year.

Hope someone can help me out.

Johan Breukelaar

Re: Set analysis with / without current selections

tresB — Fri, 20 Jun 2014 07:35:28 GMT

This is a common requirement in qv now. You would find several posts on this here in community. Please search. And have a look here : Year-over-Year Comparisons , and if you are still stuck, try sharing your sample qvw.

Re: Set analysis with / without current selections

ashfaq_haseeb — Fri, 20 Jun 2014 07:36:43 GMT

Hi Start from here.

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=

Regards

ASHFAQ

Re: Set analysis with / without current selections

ashfaq_haseeb — Fri, 20 Jun 2014 07:39:24 GMT

Hi Start from the attached URL.

I'm sure you will have better picture.

Regards

ASHFA

Re: Set analysis with / without current selections

Anonymous — Fri, 20 Jun 2014 17:11:01 GMT

You can use something like this in set analysis:

{<Month={"$(=date(floor(monthstart(Month,-1)),'MMM-YYYY'))"}>}

Month field in this case is not a text but a date data type, fromatted as 'MMM-YYYY'

Notice that I formatted the date in expression 'MMM-YYYY' becuase this is format on the Month field. Formatting is essential.

Re: Set analysis with / without current selections

breukelaarj — Mon, 23 Jun 2014 07:33:36 GMT

Unfortunatly both given examples I already tried. And again. It Always results in 0.

Below the expression I'm using to count the orders (or in my case requisitions)

count(distinct(BASKET_REQUISITIONNUMBER))

The dimension Ï'm using is a budgetcontroller. So I'm counting the number of requisitions per budgetcontroller based on month selection.

when I apply, (and Maand is dutch for Month)

count({<Maand={"$(date(floor(monthstart(Maand,-1)),'MMM-YYYY'))"}>}DISTINCT(BASKET_REQUISITIONNUMBER))

count({$<Maand={'$(=getfieldselection(Maand)-1)'}>} BASKET_REQUISITIONNUMBER)

The result is zero as in 0. So I would think it has to do with formatting. My months are displayed as jan, feb, mar etc.

A hardcoded value like {apr} does work with set analysis. So where does it go wrong. Hope you have some more idea's for me to check.

Kind regards

Johan Breukelaar

Re: Set analysis with / without current selections

jagan — Mon, 23 Jun 2014 07:39:23 GMT

HI,

Try like this for getting previous month order count

Count({<YearDimensionName=, MonthDimensionName=, DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -1))<=$(=MonthEnd(Max(DateDimensionName), -1))'}>} DISTINCT BASKET_REQUISITIONNUMBER)

Note : Replace YearDimensionName, MonthDimensionName, DateDimensionName with your actual field names.

Regards,

Jagan.

Re: Set analysis with / without current selections

tresB — Mon, 23 Jun 2014 07:42:09 GMT

For previous month, try like:

count({$<Maand={'$(=Month(AddMonths(Max(Maand),-1)))'}>} BASKET_REQUISITIONNUMBER)

Re: Set analysis with / without current selections

breukelaarj — Mon, 23 Jun 2014 07:50:01 GMT

It seems that the Month selection is cursed.

I tried another approach to to around formatting.

I have a YearMont element from my calendar. But it is formatted just to give the DD-MM-YYYY. So basically first of the month and the year.

I applied

count({<YearMonth={"$(=Date(MOnthStart(JaarMaand -1),'DD-MM-YYYY'))"}>} DISTINCT(BASKET_REQUISITIONNUMBER)))

That does give me the result of last month. So that is wrong with my month?

Re: Set analysis with / without current selections

breukelaarj — Mon, 23 Jun 2014 08:19:10 GMT

Ok. So I have it working now. Still I cannot calculate with the month, but I took a different approach where I calculate the month minus one based on the first day of the month from current selection. That does the trick......