topic Re: Sorting in a Chart in QlikView

Sorting in a Chart

Fri, 04 Nov 2016 13:08:30 GMT

I'm having the following issue.

I have a pivot table
- Dimension: Product and Date Sold.
- Expressions:
  - 1. Amount of total sales
  - 2. Percentage of total sales BY DATE

.My problem is that the percentages are displayed over the entire date range instead of just one day. See the table below for an example of what I mean.

EXAMPLE

Product	Date Sold	Amount	% of Total by Date Sold
Beer	Nov 1st	6	20% instead of 28.5% SHOULD BE OVER JUST NOV 1ST
	Nov 2nd	4	13% instead of 44% SHOULD BE OVER JUST NOV 2ND
Wine	Nov 1st	8	26.6% instead of 38% SHOULD BE OVER JUST NOV 1ST
	Nov 2nd	2	6.6 % instead of 22% SHOULD BE OVER JUST NOV 2ND
Liquor	Nov 1st	7	2.3% instead of 33% SHOULD BE OVER JUST NOV 1ST
	Nov 2nd	3	10% instead of 33% SHOULD BE OVER JUST NOV 2ND

Re: Sorting in a Chart

Anonymous — Fri, 04 Nov 2016 17:03:21 GMT

num(sum(Amount)/sum(TOTAL <[Date Sold]> Amount),'#.##0,00%')

Re: Sorting in a Chart

Fri, 04 Nov 2016 17:38:36 GMT

Robin,

This is a step in the right direction. However, this does not work. When I initially wrote the problem, I tried to simply it, because the type of business problem I'm working with is quite complex and would take a long time to explain. See my actual code below that is based off of yours. It works when I take out the second DISTINCT, which is bolded. However, I need to keep this, so that duplicates are filtered out. Any thoughts?

Count({$<Test Flag] = {'No'}>} DISTINCT Sequence)/Count({$<[Test Flag] = {'No'}>} total DISTINCT <[Call Date]> Sequence)

Re: Sorting in a Chart

Anonymous — Fri, 04 Nov 2016 17:54:13 GMT

I don't know your data, but I think you have to put distinct in front of total

Count({$<Test Flag] = {'No'}>} DISTINCT Sequence)/Count({$<[Test Flag] = {'No'}>} DISTINCT total <[Call Date]> Sequence)

Re: Sorting in a Chart

Fri, 04 Nov 2016 17:56:52 GMT

Robin,

That worked! Thanks so much for the help. Just out of curiosity, what is the difference between putting the distinct before and after Total?

-Seth

Re: Sorting in a Chart

Anonymous — Fri, 04 Nov 2016 18:05:31 GMT

Count([{SetExpression}] [DISTINCT] [TOTAL [<fld {,fld}>]] expr)

Just have a look at the QlikView help...

When using total,

"[<fld {,fld}>]" is optional exactly behind total, because

[<fld {,fld}>] belongs to total not to DISTINCT