Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Excel SUMIFS function equivalent in Qlikview Expression

Hi All,


I need one help to get a logic of Excel SUMIFS function in Qlikview Expression.


Here in attached excel, wherever BD column value is present in [Business Day - Roll to Monday] column, corresponding Dollars column values will be sum-up and comes under [Per BD] column and  [Per BD] column should be a calculated Qlikview Expression.


I have tried using below expression-


=If(BD=Aggr(NODISTINCT [Business Day - Roll to Monday],[Business Day - Roll to Monday]),Sum(total <[Business Day - Roll to Monday]> Dollars),0)


but not getting all data in [Per BD] column.


Can someone help me to find the correct logic.

1 Solution

Accepted Solutions
Highlighted

Re: Excel SUMIFS function equivalent in Qlikview Expression

May be this

=SubField(Concat(TOTAL Aggr(Sum(Dollars), [Business Day - Roll to Monday]), '|', [Business Day - Roll to Monday]), '|', BD)


Capture.PNG

View solution in original post

8 Replies
Highlighted

Re: Excel SUMIFS function equivalent in Qlikview Expression

What is the dimension of this chart where you are trying to show this?

Highlighted
Specialist
Specialist

Re: Excel SUMIFS function equivalent in Qlikview Expression

Hi,

in Script:

Mapping TMP: LOAD [Business Day - Roll to Monday],SUM(Dollars) AS Dollars
FROM [..\..\Documents\Downloads\Sample IFS function.xlsx] (ooxml, embedded labels, table is data)
Group BY [Business Day - Roll to Monday]
;



LOAD *, ApplyMap('TMP',BD,'0') AS [Per BD];
LOAD Dollars,
[Business Day - Roll to Monday],
BD
FROM
[..\..\Documents\Downloads\Sample IFS function.xlsx] (
ooxml, embedded labels, table is data);

IFCONJU.png

Highlighted

Re: Excel SUMIFS function equivalent in Qlikview Expression

May be this

=SubField(Concat(TOTAL Aggr(Sum(Dollars), [Business Day - Roll to Monday]), '|', [Business Day - Roll to Monday]), '|', BD)


Capture.PNG

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Excel SUMIFS function equivalent in Qlikview Expression

Thanks Fer for the help but I am looking for a Qlikview Expression instead of writing it in script.

Highlighted
Champion
Champion

Re: Excel SUMIFS function equivalent in Qlikview Expression

Genius

Highlighted
Contributor III
Contributor III

Re: Excel SUMIFS function equivalent in Qlikview Expression

Hey Sunny,

Thanks for the quick help. This logic is working perfectly for my requirement.

You made my day!!

Thanks a lot Brother...

Highlighted

Re: Excel SUMIFS function equivalent in Qlikview Expression

No problem at all

Please close the thread by marking correct and helpful responses.

Best,

Sunny

Highlighted

Re: Excel SUMIFS function equivalent in Qlikview Expression

Hahahaha which you just did