Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Re: Set Analysis - Previous Period % Change

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

jcampbell474

Creator III

2014-09-19
01:18 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Set Analysis - Previous Period % Change

I'm trying to calculate the % difference in volume compared to the previous period. I can compare to the previous year without issue.

Here is the syntax I'm using to get % difference to the same period, previous year:

=if(isnull([Period]),'-',(sum([Quantity])-Sum ({$<FiscalYear = {$(=Max (FiscalYear)-1)} >} [Quantity]))/Sum ({$<FiscalYear = {$(=Max (FiscalYear)-1)} >} [Quantity]))

When I change "FiscalYear" to "Period", nothing calculates. Every example I can find online uses date formatting...I'm using Periods 1-13. So, no date formatting is needed. I just need to be able to select period 7 and it show the % change from period 6 to period 7. If I just select the year, 2014 for example, I need it to show the % change in each period to the prior. At this point, comparing period 1 to period 13 of the previous fiscal year is ideal, but not necessary.

Can anyone tell me how to accomplish this?

Thanks in advance!

2,009 Views

9 Replies

sunny_talwar

MVP

2014-09-19
01:45 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

the expression your wrote up there seems to work fine. Can you send a sample of what you are working on?

Best,S

1,175 Views

jcampbell474

Creator III

2014-09-19
01:57 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Unfortunately, I don't think I can slice up the app successfully enough to post a copy of this example. I appreciate you testing my formula.

Why would the formula work using year, but not period?

1,175 Views

sunny_talwar

MVP

2014-09-19
02:33 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Have you looked at the qvw file I attached? It uses the formula with the period only and it seems to work just right.

Best,

S

jcampbell474

Creator III

2014-09-19
02:40 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Yes, I looked at it. It does work.

I replace FiscalYear with Period in my file and goes to zero (0). I removed everything and tried to get it to just calculate the previous year alone, using this: **Sum ({$<Period = {$(=Max (Period)-1)} >} [Quantity])**, but I still get zero's. Seems like it doesn't want to decrease by 1 or maybe it's a formatting thing, I don't really know.

1,175 Views

kedar_dandekar

Creator

2014-09-19
04:23 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Jason,

Are you selecting the Year in your User Interface (and not the period), in that case you will have to modify the set analysis to ignore your Year Selection..

=if(isnull([Period]),'-',(sum([Quantity])-Sum ({$<**FiscalYear=,** Period = {$(=Max (Period )-1)} >} [Quantity]))/Sum ({$<**FiscalYear =,** Period= {$(=Max (Period )-1)} >} [Quantity]))

try it out..

HTH,

KD

jcampbell474

Creator III

2014-09-19
04:46 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I tried it and got "-" in the results. What you're saying makes sense and I'll probably have to incorporate your statement into the app after this issue is resolved. Thank you for your help!

I'm now thinking it has to format. The periods are all two characters. 01, 02, etc... Formatted as text. I can subtract one in an expression and it works fine. I don't think it will work in a set analysis though. I need to create a variable to convert Period to Num, but not having any luck with it. Currently, it may be trying to subtract alpha from alpha. If I convert it to number, set analysis may view it as number and return the previous period.

Could this be it?

1,175 Views

kedar_dandekar

Creator

2014-09-20
12:46 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jason,

This could be one of the several reasons.. if you can upload a small sample of your application, it would help to locate the exact problem.

Thanks,

KD

1,175 Views

jcampbell474

Creator III

2014-09-23
08:38 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

A sample is attached. Thank you in advance for taking a look at it.

Note - I intentionally loaded two columns of "Periods". One is text. The other, number. That's how the original data is formatted.

1,175 Views

kedar_dandekar

Creator

2014-10-02
02:44 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jason,

I understand why it was difficult with set analysis, when we add a dimension to the Pivot Table (e.g. FiscalYear) , it also filters data by that dimension (i.e. FiscalYear), and only the filtered data is available for the expressions.

So, for the row where FiscalYear = 2013, the data for that row gets filtered for 2013. Hence, even if we use multiple FiscalYears (2012, 2013) in our set analysis, it does not get data for 2012.

Also, the Set Analysis that we use, is evaluated once, i.e. not by each dimension row value.

To implement your task, I have used the Total function to ignore the dimensions while calculating the expressions, and in the Set Analysis I have used multiple If statements to take care of row by row calculation.

Please find attached the updated qvw: **Prev Period Test - Total.qvw**.

HTH,

KD

1,175 Views