topic Re: Expression in chart in QlikView

Expression in chart

carolin01 — Mon, 17 Mar 2014 14:04:08 GMT

Hi,

could anybody help me with this:

sum({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}>}SoldToBusinessPartnerCount)

I´m trying to count the number of buying customers for a specific year. The field SoldToBusinessPartnerCount contains 1 for each sales position line. I think sum distinct is correct but for some reason I can´t get this working. Is it possible?

Best regards,

Carolin

Re: Expression in chart

sunilkumarqv — Mon, 17 Mar 2014 14:11:56 GMT

I think you don't need it <FYear = {$(vChooseYear1)} it will restrict year so try this

sum({$<FYear = ,OrderPhase_Desc = {$(vOrderPhaseDesc)}>}SoldToBusinessPartnerCount)

Re: Expression in chart

datanibbler — Mon, 17 Mar 2014 14:14:01 GMT

Hi Carolin,

your set_expression looks generally correct.

- Have you tested (in a textbox on the GUI) whether your variables come out right when you put them in this $()
thing?

- You might have to replace the $ at the beginning of the set_expression with a 1 to generally disregard the user's selections and place your own filters instead.

- You might have to put those variables (complete with $(), if that's correct) into double_quotes. Try both ways.

HTH

Best regards,

DataNibbler

Re: Expression in chart

carolin01 — Mon, 17 Mar 2014 14:24:17 GMT

The set_expression worked well as it is shown above. But I´m trying to change it to:

Sum(Aggr(DISTINCT({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}>}SoldToBusinessPartnerCount)))

This is necessary as I have several orders but I would like to count each customer only one time. Unfortunately I can´t get it working.

Re: Expression in chart

its_anandrjs — Mon, 17 Mar 2014 14:25:20 GMT

Hi,

I feel your expression is correct for test this expression plot a input box object and add variable there and check its value. Also check with Sum(Distict Your expresion) and check the data also.

Thanks & Regards

Re: Expression in chart

Nicole-Smith — Mon, 17 Mar 2014 14:26:09 GMT

Why not do something like this:

count({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}, SoldToBusinessPartnerCount = {1}>} distinct Customer)

Re: Expression in chart

its_anandrjs — Mon, 17 Mar 2014 14:28:06 GMT

Hi,

Ok thats right but which dimension you aggregate the sum expression you have to provide that also like

Eg: aggr( sum(Sales), Country ) Here Country is dimension

Check your dimension and put there then check it.

Thanks & Regards

Re: Expression in chart

carolin01 — Mon, 17 Mar 2014 14:39:52 GMT

Distinct Customer made that I got a 1 for each customer. But if I have it in a text box then the total is also 1 and not the number of customer that have bought that year. My next trial was:

aggr ( sum({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}>}Distinct SoldToBusinessPartnerCount) )

But also didn´t work as it showed 0.

Re: Expression in chart

Nicole-Smith — Mon, 17 Mar 2014 14:46:43 GMT

If you're using aggr(), you need to put dimensions into the aggr() function as well.

Maybe something like:

sum(aggr(count({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}, SoldToBusinessPartnerCount = {1}>} distinct Customer), Customer))

Re: Expression in chart

carolin01 — Mon, 17 Mar 2014 15:17:17 GMT

This is close now:

sum(aggr(count({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}, SoldToBusinessPartnerCount = {1}>} distinct SoldToBusinessPartnerCount), SoldToBusinessPartner))

But still some lines show the value 2 instead of 1 and I´m not sure why? SoldToBusinessParter includes the customer-number. SoldToBusinessPartnerCount includes "1" for each sales line.

Re: Expression in chart

Nicole-Smith — Mon, 17 Mar 2014 15:18:18 GMT

You want to count the distinct Customer, not the distinct count of Customer (you were counting the zeroes and ones, when you should be counting the Customers themselves):

sum(aggr(count({$<FYear = {$(vChooseYear1)}, OrderPhase_Desc = {$(vOrderPhaseDesc)}, SoldToBusinessPartnerCount = {1}>} distinct SoldToBusinessPartner), SoldToBusinessPartner))