- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Start from here.
http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=
Regards
ASHFAQ
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Start from the attached URL.
I'm sure you will have better picture.
Regards
ASHFA
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
or
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For previous month, try like:
count({$<Maand={'$(=Month(AddMonths(Max(Maand),-1)))'}>} BASKET_REQUISITIONNUMBER)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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......