Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
zyn
Contributor II
Contributor II

Sum(IF()) with two set analysis

i want to use sum if with two set analysis but its giving me wrong answer can some one help on this, below is my expression:

 

sum(
if (
WildMatch(Measure,'BHR Fail','Total Fails'),
{<DITDate= {">=$(=(date(Max(DITDate)-6)))<=$(=(date(Max(DITDate))))"},Table_Flag = {'JAT'}>}Volume,
{<DITDate= {'$(=(date(Max(DITDate))))'},Table_Flag = {'JAT'}>}Volume
)

)

Labels (4)
1 Solution

Accepted Solutions
rafaelencinas
Partner - Creator II
Partner - Creator II

Hi @zyn 

 

Instead of use Sum(If () ) try this

Sum(
{
<
Measure = {'BHR Fail', 'Total Fails'},
Table_Flag = {'JAT'},
DITDate = {">=$(=Date(Max(DITDate)-6))<=$(=Date(Max(DITDate)))"}
> +

<
DITDate = {"=$(=Date(Max(DITDate)))"},
Measure -= {'BHR Fail', 'Total Fails'},
Table_Flag -= {'JAT'}
>
} Volume
)

 

Bye!

Senior Qlik Architect
Cobra, Stallone, "You're a problem and I'm the solution"

View solution in original post

6 Replies
Chanty4u
MVP
MVP

Try this 

Sum({

    <DITDate = {">=$(=Date(Max(DITDate)-6))<=$(=Date(Max(DITDate)))"},

     Table_Flag = {'JAT'}>

    If(WildMatch(Measure, 'BHR Fail', 'Total Fails'), Volume)

)

+

Sum({

    <DITDate = {"$(=Date(Max(DITDa

te)))"},

zyn
Contributor II
Contributor II
Author

your expression in not working,
i want if my measure is 'BHR Fail','Total Fails' then it should take the whole weeks data from max date else for rest of the measure it should take only for max date,

but what is happening with my expression is that it is not taking any set analysis in sumif, it is only giving me sum of volume.

marcus_sommer

I think the logic should look like:

if(wildmatch(), Expr1, Expr2)

zyn
Contributor II
Contributor II
Author

The reason i am using Sum( IF() ) is because i want to take selection but my chart values shouldn't change ,

by using this: if(wildmatch(), Expr1, Expr2), when selecting different rows my table is taking else condition and the numbers are changing:

For my measure Measure,'BHR Fail','Total Fails', it should take whole week data and for others it should take only max date, so when i select any other row which corresponds to max date, my Measure,'BHR Fail','Total Fails' are also taking max date data, can any one suggest how to achieve this.

rafaelencinas
Partner - Creator II
Partner - Creator II

Hi @zyn 

 

Instead of use Sum(If () ) try this

Sum(
{
<
Measure = {'BHR Fail', 'Total Fails'},
Table_Flag = {'JAT'},
DITDate = {">=$(=Date(Max(DITDate)-6))<=$(=Date(Max(DITDate)))"}
> +

<
DITDate = {"=$(=Date(Max(DITDate)))"},
Measure -= {'BHR Fail', 'Total Fails'},
Table_Flag -= {'JAT'}
>
} Volume
)

 

Bye!

Senior Qlik Architect
Cobra, Stallone, "You're a problem and I'm the solution"
zyn
Contributor II
Contributor II
Author

@rafaelencinas Thanks! your solution works.