topic Re: Timeseries: % split of dimension in QlikView

Timeseries: % split of dimension

Thu, 31 Jul 2014 13:22:32 GMT

Hi there,

I have a time series chart that looks at % of total sales for each salesman for each period. My dimensions are Opsdate and Salesman and my expression is:

Sum(Sales) / Sum (TOTAL <Opsdate> Sales)

My user wants me to adjust the chart so when he selects the a Salesman the chart just drops the other salesman and the selected Salesman % stays the same. With my existing formula it just shows a flat line at 100% because both the numerator and the denominator are adjusted by the selections. I want the denominator not to be impacted by the Salesman selection. So I tried the following formula:

Sum(Sales) / Sum ( {<Salesman>} TOTAL <Opsdate> Sales

But it doesn't seem to work. I played about with Aggr functions but have had no joy.

Thanks in advance

Max

Re: Timeseries: % split of dimension

alexandros17 — Thu, 31 Jul 2014 13:27:05 GMT

try this for denominator:

Sum (1 {<Salesman>} TOTAL Sales

Re: Timeseries: % split of dimension

joshabbott — Thu, 31 Jul 2014 13:28:55 GMT

Looks like you are missing an ending parenthesis and an equal sign:

sum(Sales) / sum({<Salesman=>} TOTAL <Opsdate> Sales)

Re: Timeseries: % split of dimension

Thu, 31 Jul 2014 13:44:49 GMT

Thanks

This is good but it forces it to be the total of all the data regardless of other selections made.

Ideally what I am looking for is if I select another filter, for example Region = Europe, then the chart shows the % of Sales for each Salesman for that Region. It is only the Salesman dimension that I want to be ignored in the denominator.

Re: Timeseries: % split of dimension

joshabbott — Thu, 31 Jul 2014 13:48:12 GMT

The <Salesman> should be <Salesman=> to ignore the selection in the set modifier portion of the set analysis

Re: Timeseries: % split of dimension

alexandros17 — Thu, 31 Jul 2014 13:52:04 GMT

You're right, so try:

Sum(Sales) / Sum (TOTAL <Salesman > Sales)

let me know

Re: Timeseries: % split of dimension

Thu, 31 Jul 2014 14:04:42 GMT

You can actually use either, both give the same result of ignoring the Salesman selection

Re: Timeseries: % split of dimension

Thu, 31 Jul 2014 14:36:51 GMT

Thanks Josh, I would have thought that formula should work but it the Salesman bit doesn't seem to do anything, when I select a single Salesman it still shows as 100%. Could it be because Salesman is one of my dimensions in the chart?

Re: Timeseries: % split of dimension

Thu, 31 Jul 2014 14:38:14 GMT

Thanks for your help. We are getting closer but this formula ignores the date so the chart shows each weeks sales for each salesman divided by total sales over all periods?

Re: Timeseries: % split of dimension

joshabbott — Thu, 31 Jul 2014 14:41:18 GMT

I really don't think so. Is there another expression in your chart? Is the 'Salesman' the exact same dimenstion that is in your dimension, I think if this is cased wrong or spelled different, it may do this.

Re: Timeseries: % split of dimension

alexandros17 — Thu, 31 Jul 2014 14:41:33 GMT

Then use:

Sum(Sales) / Sum (TOTAL <Salesman, OpsDate> Sales)

Re: Timeseries: % split of dimension

Thu, 31 Jul 2014 15:04:57 GMT

Hey Guys, I have added an example to show what I am trying to do if this makes it any clearer. So the issue is when I click on a Salesman the chart goes to 100%, I want it to stay with the same values just drop the unselected salesman from the chart.

Thanks!

Re: Re: Timeseries: % split of dimension

joshabbott — Thu, 31 Jul 2014 15:08:24 GMT

Try this:

Another thing I noticed is the color changes of the salesman. Check 'Persistent Colors' on the 'Colors' tab so this doesn't happen if you don't want it to.

Re: Timeseries: % split of dimension

Thu, 31 Jul 2014 15:41:39 GMT

Thanks Josh. The reason the formula didn't work in my live version is the field was called SalesMan and I had Salesman in my expression! Just one of those days. Much appreciated your help everyone.