topic Re: sum where condition in QlikView

sum where condition

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

Hello,

I have a 3 pivot tables with dimention the store and 3 expressions

people counter	transactions	CR
store1 100	15	15%
store 2 30	7	23%
store 3 0	5	0%

The CR expression is column(2)/column(1) and the other two expressions are sum(people counter) and sum(transactions)

The first pivot is for the day the 2nd for the month and the 3rd for the year.

For some days the counter of the people doesnt work properly and gives 0.When I want to calculate the total CR for the year or the month

I want to not to sum the days where the CR was 0 .

Can somone give the set analysis where the expression will the sum of transactions WHERE THE PEOPLE COUNTER WAS NOT 0?

Thank you in advance

Re: sum where condition

sunny_talwar — Tue, 27 Feb 2018 17:45:54 GMT

May be like this

Sum({<Store = {"=Sum([people counter]) <> 0"}>}transactions)

Re: sum where condition

pablolabbe — Tue, 27 Feb 2018 19:25:04 GMT

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others

Re: sum where condition

kostiskampouris — Wed, 28 Feb 2018 08:58:54 GMT

Hello Sunny and thank you for your answer.

I tried your suggetion ,it didn't work but I changed it to

Sum({<date = {"=Sum([people counter]) <> 0"}>}transactions) and it worked.

My problem is that the pivot is a little more complicated than I described in the initial post

In the month table for example for counting the transactions I have the expression

Sum({<date = {">=$(=MonthStart(Max(trn_date))) <=$(=Max(trn_date))"}>} transactions)

(the user selects one day and sees the sum of the transactions from the beggining of the month until the chossen day)

How can I insert the condition that the above sum(of the transactions) excludes the days where the people counter was 0.

I tried something like :

Sum({<date = {">=$(=MonthStart(Max(trn_date))) <=$(=Max(trn_date))"}>*<date = {"=Sum([people counter]) <> 0"}>} transactions)

but it retruns 0.

Sorry if my questions are novice of silly,but I dont have the experience to understand the mistake.

Re: sum where condition

sunny_talwar — Wed, 28 Feb 2018 10:00:21 GMT

May be this

Sum({<date = {"=Sum([people counter]) <> 0 and date >= MonthStart(Max(trn_date)) and date <= Max(trn_date)"}>} transactions)