9 Replies Latest reply: Jun 23, 2014 4:19 AM by Johan Breukelaar

Set analysis with / without current selections

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

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

Hi Start from the attached URL.

I'm sure you will have better picture.

Regards

ASHFA

• Re: Set analysis with / without current selections

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

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)

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)

or

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

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

For previous month, try like:

• Re: Set analysis with / without current selections

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