Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

How do I get an expression for previous month sales?

I'm trying to get an expression that can be put on a chart showing the difference between current month and previous month sales. I have the months serialized. I used set analysis as follows

sum(Sales) - Sum({1<Serial_Month = {$(=Only(Serial_Month)-1)}>} Sales)

It works only when one month is selected. However the results set show 2 rows, one with the selected month and one with the previous month sales.

I would like to have only the selected month row and have the previous month on the same row.

Hope this is understandable.

Thanks.

JM

8 Replies
MVP
MVP

How do I get an expression for previous month sales?

Set analysis only works when one month is selected because a set is only evaluated once for the whole chart, not once per row in the chart.

Generate an AsOf table:

AsOfMonth, MonthType, Month
Jan 2009, Current, Jan 2009
Jan 2009, Previous, Dec 2008
Feb 2009, Current, Feb 2009
Feb 2009, Previous, Jan 2009
etc.

Use AsOfMonth as your dimension instead of Month. If you just want to show the numbers next to each other, use a pivot table and add MonthType as a dimension. Your expression is just sum(Sales). But it sounds like you want to see them both AND see the difference. In that case:

Current Month Sales = sum({<MonthType={'Current'}>} Sales)
Previous Month Sales = sum({<MonthType={'Previous'}>} Sales)
Difference = "Current Month Sales" - "Previous Month Sales"

Attached is an example using previous years and showing a percentage difference, but it's the same idea.

Not applicable

How do I get an expression for previous month sales?

Thank you. This put me on the right path.

JM

Not applicable

Re: How do I get an expression for previous month sales?

Hi John,

I have a similar problem im wondering if you can help with.

When a user selected a particular weekending from a list, there should be only 1 row containing the NEW ACTIVITY and a PREVIOUS ACTIVITY on same row.  What is going wrong here is that the 'NEW activity previous' is getting added but not to the correct row. 

E.g user here has selected 8-may-2011, therefore 01-may-2011 should not be showing although the 'New activity previous' should be showing on row 8-may-2011.  The trend arrow is based on the change from New Activity (Previous) to New Activity

untitled.bmp

My QVW is attached for your viewing.

Kind regards

?????

MVP
MVP

How do I get an expression for previous month sales?

Same problem - set analysis doesn't know about your row value, so the results end up on the week you calculated, which is to say on a different row.  Same solution - create an AsOf table of your weeks, and connect each AsOfWeek to both the current and previous week.  If you will ALWAYS have a single week ending date selected, there might be an easier solution, but I don't know if that's your situation.

I'll also caution against using calculated expressions in list boxes and calculated dimensions in charts.  If at all possible, replace these with real dimensions, calculating them in the script as required.  Much better to calculate such things once in the script and not every time anyone looks at the list box or chart.

Not applicable

Re: How do I get an expression for previous month sales?

Hi john,

I amended my qvw to include an asofweek table.  I also removed dimension expressions as advised.

The problem still exists where there are still 2 rows when user selects a particular week.  'Previous' is also not on the row it should be. The strange thing with this method is that ive also lost the true value of 'previous', it appears the same as current.

untitled.bmp

Do you where im going wrong? my file attached.

Regards

MVP
MVP

Re: How do I get an expression for previous month sales?

I should probably start preemptively explaining this particular problem rather than waiting for people to ask about it.  For the AsOf approach to work, you not only need to use the AsOfWeek field in your chart, but you also need to make selections in it instead of in the Week field.

What happens when you select regular old week 10?  Well, it's linked directly to the week 10 data, so only week 10 data is selected.  And where does this week 10 data show up in our chart?  Week 10 is the current week for AsOfWeek 10, and it's the previous week for AsOfWeek 11.  So you see week 10 data only, but in two different columns and two different rows of your chart.  It's technically correct and matches what was requested, but isn't what anyone likely wanted when they selected week 10.

So you'll at least need to add the AsOfWeek as a selection.  But when users are presented with multiple Week selections, they could easily get confused.  So my advice would be to ONLY allow them to make selections in the AsOfWeek.  Then on open of the application, trigger selection and lock of 'Current' for your PreviousCurrentFlag.  That way, all charts and other objects like list boxes will behave as if you'd actually made a selection in the Week field.  But when necessary, you can use set analysis to override this, such as when you need to compare the current week to the previous week.

I should note that I've never used an AsOf table in a live application - I've simply never needed to do these sorts of date comparisons for some reason.  So this is mostly theorycraft on my part, though I've recently started using the "lock then set analysis override" approach successfully for other types of data models in some of my live applications.  But since I've never done it with an AsOf table, I may have failed to think of possible issues.

If you are allowing only selection of the AsOfWeek, it might be best to just call it Week, and rename the real Week field to something else.  Users will probably understand selecting a Week more than selecting an AsOfWeek.  I've not done that in the attached example because I can't do a reload due to all the data sources.

Anyway, the attached example demonstrates what I'm saying.  The 'current' columns in your existing chart no longer need set analysis to pick the current week, and I've added a new chart demonstrating this as well.

Not applicable

Re: How do I get an expression for previous month sales?

Hi John, many thanks for your help as always youve proven to be a valuable source of Qlikview help.

The example attached works just as requested.  I guess i didnt grasp the fact that 'Current' had to be selected (or locked in your example).

I agree that we dont want to show the 'PreviousCurretFlag' to the user, so maybe a trigger would be the best way.  The thing is i want 'Current' to be selected all the time, even without appearing in the 'Current Selections' box.  Do you reccommend a way of achieving this?

Regards

Jas

MVP
MVP

Re: How do I get an expression for previous month sales?

You can of course just select and lock the field manually, then remove the list box.  But I feel like a trigger on open to select and lock 'Current' is safer, yes.

To hide the field selection, in the script:

SET HidePrefix = %; // or whatever you want

And then change the name of the field to %PreviousCurrentFlag, and it won't show up in the current selections box.