Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm having the following issue.
.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 |
num(sum(Amount)/sum(TOTAL <[Date Sold]> Amount),'#.##0,00%')
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)
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)
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
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