topic Re: Excel SUMIFS function equivalent in Qlikview Expression in QlikView

Excel SUMIFS function equivalent in Qlikview Expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Excel SUMIFS function equivalent in Qlikview Expression

sunny_talwar — Tue, 02 Jan 2018 14:45:48 GMT

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

Re: Excel SUMIFS function equivalent in Qlikview Expression

el_aprendiz111 — Tue, 02 Jan 2018 14:50:29 GMT

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);

Re: Excel SUMIFS function equivalent in Qlikview Expression

sunny_talwar — Tue, 02 Jan 2018 14:58:59 GMT

May be this

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

Re: Excel SUMIFS function equivalent in Qlikview Expression

Anonymous — Tue, 02 Jan 2018 15:25:32 GMT

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

Re: Excel SUMIFS function equivalent in Qlikview Expression

YoussefBelloum — Tue, 02 Jan 2018 15:28:46 GMT

Genius

Re: Excel SUMIFS function equivalent in Qlikview Expression

Anonymous — Tue, 02 Jan 2018 15:31:47 GMT

Hey Sunny,

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

You made my day!!

Thanks a lot Brother...

Re: Excel SUMIFS function equivalent in Qlikview Expression

sunny_talwar — Tue, 02 Jan 2018 15:32:28 GMT

No problem at all

Please close the thread by marking correct and helpful responses.

Best,

Sunny

Re: Excel SUMIFS function equivalent in Qlikview Expression

sunny_talwar — Tue, 02 Jan 2018 15:32:44 GMT

Hahahaha which you just did