Excel SUMIFS function equivalent in Qlikview Expression

[Anonymous]

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.

[sunny_talwar]

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

[el_aprendiz111]

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

[sunny_talwar · Accepted solution]

May be this

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

[Anonymous]

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

[YoussefBelloum]

Genius

[Anonymous]

Hey Sunny,

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

You made my day!!

Thanks a lot Brother...

[sunny_talwar]

No problem at all

Please close the thread by marking correct and helpful responses.

Best,

Sunny

[sunny_talwar]

Hahahaha which you just did