Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Expression Help

Hello:

I am hoping to calculate a Volume Change metric as an expression.  An abbreviate d version of my table structure is as follows:

Geography          Time Period          Target          Volume

Boston                 QTD                      ONC            25

Boston                QTD                      Other           27    

Boston                1 Month                 ONC           10

Boston                1 Month                 Other           8

Boston                3 Months               ONC            30

Boston                3 Months               Other           25

New York           QTD                       ONC            43



To achieve a total volume metric, my expression looks something like this:

     =sum(Volume)

But now I want to do a Volume Change metric: (Current Time Period Volume - Prior Time Period Volume) / Prior Time Period Volume

How would I go about achieving such an expression with my current table setup??

I have a feeling that I would need to add another column for 'Previous Volume' but I do not really want to do this.

Any/all help is most appreciated.

Thank You!!

Tags (2)
4 Replies
Employee
Employee

Re: Expression Help

In your abbreviated data the only field that gives any time context is 'time period' but its bucketted into restricive / pre-aggregated values like QTD, 1 Month, 3 Month.

If you can access a more granular source of data that just has Dates in it , you can use many techniques in qlik to show any bucket of dates for period vs period comparison.

Do you have access to the raw values reported by date or date/time ?

QlikDeveloper
New Contributor II

Re: Expression Help

Hi,

I have a straight table,in which we use expressions to show the "Profit" and other details  for each product.

We calculate the "Profit" on the fly(depending on the selections).In the same table we have a "Range" column,which is calculated on the basis of "Profit". Now, I need another object to select the "Range".

Product Name(Not Null)Mfg Date(Not Null)ProfitRangeQuantity
abc1/10/2014700000-1000004
xyz1/10/2014500000400001-5000005
asdf1/10/2014150000100001-4000006

To elaborate,the above table is my staright table.I have some predefined "Range",Like 0-100000,100001-400000 ,400001-500000 .Now I need another object(List Box or anything) of "Range" so that I can select the table depending on the range.

How to implement this logic?

stevenrtaylor
Contributor II

Re: Expression Help

I'm assuming you have more than those time periods in your system.

For example, if you have '1 Month' do you also have 'Last Month'?

If so:

Volume change = sum({$<[Time Period]={'1 Month'}>}Volume)- sum({$<[Time Period]={'Last Month'}>}Volume)

Or something along those lines since I can't see much from your post.

Employee
Employee

Re: Re: Expression Help

Here are 2 approaches to creating a list box to select range.

Both pre-aggregate the profits to the Product / Date combination granularity. So when you select a range it is going to show you product/date combinations that have that amount of profit.

The Class function is great for this because its 100% dynamic. However the bucket sizes/ranges are fixed. Below i set to 100000.

Nested If provides more customized bucket size but the expression is longer .

One issue is that both methods only bucket for the range that you have in the live data so you will notice missing buckets.

There is probably a not too difficult way of handling that if you expect gaps in your data / ranges .

Capture.PNG.png