Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Fabien
New Contributor III

Percentage of sum with 2 fields

Hi all,

I am trying to show what i thought was a simple % based on a specific field and week number.

The following expression returns a 'OK', however it is showing 0% which should not be the case.

What am i missing here?

=If(GetSelectedCount([Week Num])=0,

Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)
/
sum(total TEU)

,

Sum({$<[Quote Type]={"DQ"}>} TEU)
/
sum(total TEU))
Labels (1)
1 Solution

Accepted Solutions
Highlighted
Fabien
New Contributor III

Re: Percentage of sum with 2 fields

Here is the solution, i had to divide by the total TEU for the previous week:

=If(GetSelectedCount([Week Num])=0,
(Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)/Sum({$<[Week Num]={"$(=week(today())-1)"}>} TEU)),
Sum({$<[Quote Type]={"DQ"}>} TEU)/sum(TEU))

View solution in original post

6 Replies
Highlighted

Re: Percentage of sum with 2 fields

What if you add something

=Sum(Aggr(If(GetSelectedCount([Week Num])=0,

Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)
/
sum(total TEU)

,

Sum({$<[Quote Type]={"DQ"}>} TEU)
/
sum(total TEU)), Your Dim))

 

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Fabien
New Contributor III

Re: Percentage of sum with 2 fields

Thanks for your response Anil.

Unfortunately it is still showing 0% 🤔

Highlighted
Partner
Partner

Re: Percentage of sum with 2 fields

Hi Fabien

Try using this

=If(GetSelectedCount([Week Num])=0,

Sum({$<[Quote Type]={'DQ'}, [Week Num]={"$(=week(today())-1)"}>} TEU)
/
sum({1}total TEU)

,

Sum({$<[Quote Type]={'DQ'}>} TEU)
/
sum({1}total TEU))

 

Hope it helps

Thanks,

Highlighted
asinha1991
Contributor III

Re: Percentage of sum with 2 fields

which part of the if else is failing?, try to put constant like 1 and 2 for example and see if it works.

if it is failing when you have no week selected, try to check this in a text box

Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)

maybe weeknum is not equating properly?

also try using single quote for [Quote Type]={'DQ'},

Highlighted
Fabien
New Contributor III

Re: Percentage of sum with 2 fields

Thank you all for your replies.

The part that is failing is the first calculation:

Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)
/
sum(total TEU)

When i select a Week Num the #KPI box works fine and displays the correct value.

I have tried other object, but nothing seems to do the trick.

Highlighted
Fabien
New Contributor III

Re: Percentage of sum with 2 fields

Here is the solution, i had to divide by the total TEU for the previous week:

=If(GetSelectedCount([Week Num])=0,
(Sum({$<[Quote Type]={"DQ"}, [Week Num]={"$(=week(today())-1)"}>} TEU)/Sum({$<[Week Num]={"$(=week(today())-1)"}>} TEU)),
Sum({$<[Quote Type]={"DQ"}>} TEU)/sum(TEU))

View solution in original post