Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
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)
Highlighted
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'},

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