topic Re: Simple Expression (Above/Below) in QlikView

Simple Expression (Above/Below)

Tue, 24 Jun 2014 17:22:57 GMT

Hi,

I have a very primitive question.

I have data that is provided to me on a daily basis. This data that is provided is only provided in a cumulative fashion.

Example

Total Figures

6/24 - 25

6/23 - 20

6/22 - 15

I have an expression that gives me the added figures from below (works perfectly): sum(Figures)-below(sum(Figures)

6/24 - 5

6/23 - 5

6/22 - 5

However, when I try to add a dimension, my formula breaks.

The Figures are divided into categories. I would like to see, which categories are my daily figures coming from.

Example

6/24 - 25

Item A - 15

Item B - 10

6/24 - 20

Item A - 15

Item B - 5

My expression should be able to show that Item B was the only category to add values while item A stayed consistent.

In my Dimensions tab, I only have Date. I would like to include product in here and a formula that will provide me with the correct information I would like to see.

Please help.

Re: Simple Expression (Above/Below)

vgutkovsky — Tue, 24 Jun 2014 18:19:21 GMT

May there's something you left out...? Take a look at the attached, your logic should be working.

Regards,

Vlad

Re: Simple Expression (Above/Below)

Tue, 24 Jun 2014 18:22:13 GMT

Here is an example of what the data should read (daily figure)

Date	Product	Figure	Daily Figure

6/24	A	15	0
6/24	B	10	5
6/23	A	15	5
6/23	B	5	5
6/22	A	10	10
6/22	B	0	0

Right now, the below function that I am using is taking the immediate value in the row below, regardless of the product.

So the data reads 5, -5, 10, -5, 10

I would like my expression to somehow understand to give me a total by day, by product.

Re: Simple Expression (Above/Below)

vgutkovsky — Tue, 24 Jun 2014 18:29:44 GMT

It would only do that if you use the word "total" in the below() function. See my example above--as you can see, it is respecting Item. You may need to post a reduced example for someone to be able to help out.

Regards,

Vlad

Re: Simple Expression (Above/Below)

Tue, 24 Jun 2014 18:32:32 GMT

Also, I'm using a straight table.

The reason why I am using this is because I'm trying to make a daily bar graph of new figures (separated by product) over time.

Re: Simple Expression (Above/Below)

Tue, 24 Jun 2014 18:43:01 GMT

Hi,

Thank you for your help. Can you show me what you are talking about in regards to the total function?

What you have is correct... but you are using a pivot table.

Can you give me the same results in a straight table?

I would like this in a straight table so I can transfer the information to a chart seamlessly.

Re: Simple Expression (Above/Below)

Tue, 24 Jun 2014 18:51:44 GMT

The ultimate goal is to put this into a bar chart over time.

Each day will have the new total daily figures separated by product (one bar each day divided into various products).

Re: Simple Expression (Above/Below)

vgutkovsky — Tue, 24 Jun 2014 21:18:14 GMT

You get the exact same results in a straight table...just switch the object type in my example and see for yourself.

Vlad

Re: Simple Expression (Above/Below)

Tue, 24 Jun 2014 21:20:45 GMT

I understand. Mainly what I am trying to do is present this as a graph.

Can you please help me? I would like to display the information over time and have a single bar for each date (new figures only). Each bar should be split by product.

Re: Re: Simple Expression (Above/Below)

vgutkovsky — Tue, 24 Jun 2014 21:37:51 GMT

Ah, well that's a bit harder. The reason it's harder is that your date needs to be your first dimension and not your second one--function below() won't work in that case. You would need to actually use function above() encased in an aggr() that would reverse the chart dimensions. See attached.

Regards,

Re: Re: Simple Expression (Above/Below)

Tue, 24 Jun 2014 22:10:23 GMT

Thank you.