topic Re: Sum If Expression in Reporting Service & Alerting

Sum If Expression

surtex — Fri, 15 Nov 2024 22:05:07 GMT

Hey Qlik-Member,

I'm currently stuck with data visualization. Following I'll describe my problem:

=If([PC_Mapping*]='00000001850999011200' or [PC_Mapping*]='00000001850999011201',(Sum({<Cost_Center = {'1940146024','1940146026','1940146084'}>} Cost_Center_Amount), [Sum(Cost_Center_Amount)]))

If the Profit Center = 00000001850999011200, 00000001850999011201 then I need the Summation of Cost_Center_Amount by the Cost Centers = 1940146024, 1940146026,1940146084 and for all other Profit Center I need the Summation of Cost_Center_Amount by all Cost Centers.

I hope you understand my problem and can help me.

Thanks in advance.

Best regard

Josh

Re: Sum If Expression

luizcdepaula — Mon, 08 Aug 2022 17:29:35 GMT

Hi Josh,

I don't see anything wrong with the logic, the only problem seems that you might be placing the brackets in the wrong place. Also, does the field PC_Mapping* has an asterisk in it? If so, try the below:

If([PC_Mapping*]='00000001850999011200' or [PC_Mapping*]='00000001850999011201',Sum({<Cost_Center = {'1940146024','1940146026','1940146084'}>} Cost_Center_Amount),Sum(Cost_Center_Amount))

I hope it works.

Luiz

Re: Sum If Expression

surtex — Tue, 09 Aug 2022 06:09:52 GMT

Hey Luiz,

thank you for your commitment. I have tried different brackets, but it was not successful..

Without the else-condition, the expression works and I get the sum of cost center amount by the cost center I have listed...

Anyone have an idea??

Thanks.

Re: Sum If Expression

BrunPierre — Tue, 09 Aug 2022 09:11:21 GMT

Maybe this in the script;

IF([PC_Mapping*] = '00000001850999011200' OR [PC_Mapping*] = '00000001850999011201' AND MATCH(Cost_Center,'1940146024','1940146026','1940146084'),Cost_Center_Amount,
Cost_Center_Amount) AS New_Cost_Center_Amount

Re: Sum If Expression

surtex — Tue, 09 Aug 2022 09:42:03 GMT

Hey again,

sorry Luiz, i had a comma error.. it works fine, thanks 🙂

But now i take the next problem:

Related to the expression above, I want a year limit without setting a extra filter..

Can anybody help me?

Thanks 🙂

Re: Sum If Expression

luizcdepaula — Tue, 09 Aug 2022 12:56:10 GMT

Hi Josh,

I am glad it worked. On the year limit, can you be more specific? you want to show a year worth of data, like 365 days every time, or set it current year?

I always like to set my logic to Max(Year) so it defaults to current year for me, then every time other years are selected, only one year at a time will show, which will be the Max year selected.

Let me know if it helps.

Luiz

Re: Sum If Expression

surtex — Tue, 09 Aug 2022 16:49:29 GMT

Yeah, sure.

I have a bar chart with four KPI‘s.

1) Actual Year to Date Sales (no problem)

2) Forecast Sales (no problem)

3) Sales Previous Year (Year 2021 for example, it’s a problem)

4) Sales Percentage (no problem)

Now I create for each bar a KPI and I would like to show the 3. KPI without setting a separat filter anyway. It is possible to paste a year in the expression above? I hope you can understand me. Otherwise I will show you the problem by posting snippings.

Regards

Josh

Re: Sum If Expression

luizcdepaula — Tue, 09 Aug 2022 17:25:32 GMT

Try the below:

If([PC_Mapping*]='00000001850999011200' or [PC_Mapping*]='00000001850999011201',Sum({<Cost_Center = {'1940146024','1940146026','1940146084'},Year={"$(=Year(today())-1)"}>} Cost_Center_Amount),Sum({<Year={"$(=Year(today())-1)"}>}Cost_Center_Amount))

Luiz

Re: Sum If Expression

surtex — Thu, 11 Aug 2022 09:13:36 GMT

Hey Luiz,

unfortunately the expression doesn’t work, so I get no value at all.

I don‘t know but I think it‘s because too many conditions have now been met or isn’t that a problem for Qlik?

The database behind is structured as follows:

1) Raw Data (QVD File):

Columns: Cost_Center, Cost_Center_Amount, Cost_Element,Month, Year

2) Profit Center / Cost Element Mapping (Excel):

Columns: Cost_Center, Profit_Center, Profit_Center_Code (00000001850999011201,…)

Thanks for your help, I‘m very grateful to you.

Re: Sum If Expression

luizcdepaula — Thu, 11 Aug 2022 15:00:20 GMT

Hi Josh,

It doesn't have to do with the number of conditions in this case. If that was the issue, you would get a different error, indicating the calculations were unable to finish. If you are getting blank results, it has probably to do with the way the data model is designed.

Try the condition on the year, without the Profit Center condition and see if it works.

Luiz