Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sorting in a Chart

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

ProductDate SoldAmount% of Total by Date Sold
BeerNov 1st620% instead of 28.5% SHOULD BE OVER JUST NOV 1ST
Nov 2nd413% instead of 44% SHOULD BE OVER JUST NOV 2ND
WineNov 1st826.6% instead of 38% SHOULD BE OVER JUST NOV 1ST
Nov 2nd26.6 % instead of 22% SHOULD BE OVER JUST NOV 2ND
LiquorNov 1st72.3% instead of 33% SHOULD BE OVER JUST NOV 1ST
Nov 2nd 310% instead of 33% SHOULD BE OVER JUST NOV 2ND
5 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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)

Anonymous
Not applicable
Author

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)

Not applicable
Author

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

Anonymous
Not applicable
Author

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