Sum If Expression

[surtex]

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

[luizcdepaula · Accepted solution]

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

[surtex]

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.

[BrunPierre]

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

[surtex]

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 🙂

[luizcdepaula]

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

[surtex]

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

[luizcdepaula]

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

[surtex]

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.

[luizcdepaula]

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