Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
roharoha
Valued Contributor III

Re: Sorting in a Chart

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

Not applicable

Re: Sorting in a Chart

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)

roharoha
Valued Contributor III

Re: Sorting in a Chart

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

Re: Sorting in a Chart

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

roharoha
Valued Contributor III

Re: Sorting in a Chart

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

Community Browser