Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Partner - Contributor

## 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

9 Replies
MVP

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.

Champion III
Champion III

Hi Start from the attached URL.

I'm sure you will have better picture.

Regards

ASHFA

Anonymous
Not applicable

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.

Partner - Contributor
Author

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

Luminary Alumni

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.

MVP

For previous month, try like:

Partner - Contributor
Author

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