topic Re: percentage, total and evolution in QlikView

percentage, total and evolution

Wed, 25 Nov 2020 16:16:04 GMT

Hello there,

working on a draw i have that kind of graph

LOAD Nom,
Prénom,
[Adresse e-mail],
Status,
Langue,
[Champ libre 1],
[Champ libre 2],
Country,
subfield(Country,'-',1) as CorrectedCountry,
Date(Floor([Date de création])) as Date,
year([Date de création]) as year,
month([Date de création]) as month,
day([Date de création]) as day,
hour([Date de création]) as hour,
Time(Frac([Date de création])) as Time

this is my expression, quite simple

=count(DISTINCT [Adresse e-mail])

the dimension is DAY

questions are the following...

TOTAL : i need to add another graph showing the evolution ...in my case 4824, first day, 14967 second day..... what would be the best expression to calculate and to show this.

PERCENTAGE : i need to add somewhere the evolution in percentage between day -1 and day+1 what do you recommend to use ?

EVOLUTION : last point, i need to do an estimation of the progression...meaning that i need to estimate the total number of participants knowing the fact that the draw will run 10 to 12 days...

Thanks for your help...quite simple question i know

UPDATE : Something strange happening look at the png file ...

Re: percentage, total and evolution

MK_QSL — Wed, 11 May 2016 08:19:44 GMT

Haven't tested but you can try it..

TOTAL

RangeSum(Above(count(DISTINCT [Adresse e-mail]),0,RowNo()))

Percentage

=(Above(count(DISTINCT [Adresse e-mail])) - count(DISTINCT [Adresse e-mail]))/Above(count(DISTINCT [Adresse e-mail]))

Re: percentage, total and evolution

sunny_talwar — Wed, 11 May 2016 08:20:08 GMT

May be this:

1) TOTAL

RangeSum(Above(Count(DISTINCT [Adresse e-mail]), 0, RowNo()))

2) PERCENTAGE

Not sure what you are looking for here, can you elaborate

3) EVOLUTION

Not sure what you are looking for here, can you elaborate

Re: percentage, total and evolution

Wed, 11 May 2016 08:24:58 GMT

Hello.

2 Percentage

to be able to track evolution dayby day. like between day 1 and day 2 we had + 20% between day 2 and day 3 we had +23% etc....

3. Evolution.

we have so far around 30'000 participants, if the figures are still growing at the same pace (%) , what would be the total number of participants in 10 days...

is that clearer ?

Re: percentage, total and evolution

Wed, 11 May 2016 08:30:43 GMT

what is the best solution for the evolution part ???

Re: percentage, total and evolution

sunny_talwar — Wed, 11 May 2016 08:33:30 GMT

Not entirely sure, but may be this:

=Pow((Above(count(DISTINCT [Adresse e-mail])) - count(DISTINCT [Adresse e-mail]))/Above(count(DISTINCT [Adresse e-mail])), 10) * RangeSum(Above(Count(DISTINCT [Adresse e-mail]), 0, RowNo()))

=Pow((Above(count(DISTINCT [Adresse e-mail])) - count(DISTINCT [Adresse e-mail]))/Above(count(DISTINCT [Adresse e-mail])), 10) * Count(TOTAL DISTINCT [Adresse e-mail])

=(Pow(1+(Above(count(DISTINCT [Adresse e-mail])) - count(DISTINCT [Adresse e-mail]))/Above(count(DISTINCT [Adresse e-mail])), 10) - 1) * RangeSum(Above(Count(DISTINCT [Adresse e-mail]), 0, RowNo()))

=(Pow(1+(Above(count(DISTINCT [Adresse e-mail])) - count(DISTINCT [Adresse e-mail]))/Above(count(DISTINCT [Adresse e-mail])), 10) - 1) * Count(TOTAL DISTINCT [Adresse e-mail])

Re: percentage, total and evolution

Wed, 11 May 2016 08:40:21 GMT

no result on both expression...:(

Re: percentage, total and evolution

sunny_talwar — Wed, 11 May 2016 08:41:48 GMT

I updated the expression, but it won't make a difference if you are getting null values. Would you be able to play around with the expressions to make sure I have placed all the parenthesis at the right spot in my new expression

Re: percentage, total and evolution

Wed, 11 May 2016 08:49:30 GMT

not sure that i have the level but i will try

Re: percentage, total and evolution

sunny_talwar — Wed, 11 May 2016 08:51:02 GMT

May be it would be easier if you can share a sample with the expected output

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: percentage, total and evolution

Wed, 11 May 2016 08:55:06 GMT

Hello

how to include the "DAY" data into your percentage

=(Above(count(DISTINCT [Adresse e-mail])) - count(DISTINCT [Adresse e-mail]))/Above(count(DISTINCT [Adresse e-mail]))

what would be interesting for my Dashboard is to had a figure showing what is the evolution in percentage between yesterday and today .

Best

Re: percentage, total and evolution

Anonymous — Wed, 11 May 2016 09:04:02 GMT

count(field name)/count(total field name)

Re: percentage, total and evolution

ashishkalia — Wed, 11 May 2016 09:09:33 GMT

Hey jphfavre,

Take Dimension Day
Total = count( {<Day ={ '<=$( = Max(Day) )' }>}DISTINCT EMAIL)
Percentage = (count( {<Day ={ '<=$( = Max(Day) )' }>}DISTINCT EMAIL) - count( {<Day ={ '<$( = Max(Day) )' }>}DISTINCT EMAIL) ) / count( {<Day ={ '<=$( = Max(Day) )' }>}DISTINCT EMAIL)
Not sure what you looking for third one.

Please tell if this is working or not.

Re: percentage, total and evolution

Wed, 11 May 2016 09:25:26 GMT

Thanks

for the third is the following. Knowing that we still have 10 days of competion and knowing that each we increase the number of participants by X%, how many participants could we estimate at the end of the competition....40'000, 50'000, etcc...more ...

is that clearer ?

Re: percentage, total and evolution

ashishkalia — Wed, 11 May 2016 09:35:53 GMT

Is it what you looking for:-

count( {<Day ={ '<=$( = Max(Day) )' }>}DISTINCT EMAIL)+( count( {<Day ={ '<=$( = Max(Day) )' }>}DISTINCT EMAIL)*vForcast/100 )

Where vForecast is a variable which you can assign to inputbox and let user decide the % of forecasting.

Re: percentage, total and evolution

Wed, 11 May 2016 09:44:51 GMT

looking good but how to implement the fact that the competition will still run for 10 days...