topic Chart Help in QlikView

Chart Help

Tue, 16 Nov 2010 20:34:33 GMT

I need some help with a chart. I have been able to create a list box with the following expression

if (count ([SAP Account #]) <= 1 , newcustomer, currentcustomer)

This tells me if the sap account number exists multiple times in the data and returns new or current customer.

What I want to do now is to chart the % of new vs. current

to get the total customers (or division portion) i am using count(distinct [sap account #])

what I can't figure out how to do is to calculate the total number of current or new (rather than just displaying the text).

expression 1 would be something like this in more of a practical programming sense (minus proper syntax)

-------------------

loop through account numbers

if (count ([SAP Account#]) <=1, newcustomer + 1, currentcustomer +1)

expression 1: newcustomers/total customers * 100

express 2: current customers/total customers * 100

-----------

I am really stuck on what the expression would be and any help would be much appreciated.

Thanks

Chart Help

vgutkovsky — Tue, 16 Nov 2010 21:22:52 GMT

For something like this, it's better to create a flag in your script to identify new customers. Then you can use simple set analysis to chart the expressions:

Expression #1: count(distinct {<NewCustomerFlag={1}>} [SAP Account #])/count(distinct [SAP Account #])
Expression #2: count(distinct {<NewCustomerFlag={0}>} [SAP Account #])/count(distinct [SAP Account #])

where NewCustomerFlag is the flag you create in your script. It would be even better if you can create a flag for each distinct account # so you can just sum the flag (this will work much faster than counting distinct).

Regards,

Chart Help

Tue, 16 Nov 2010 21:44:20 GMT

so something like this

if (count ([SAP Account #]) <= 1 ,1, 0) as NewCustomerFlag

then the newcustomerflag = 1 would be the true (< or = 1)

and newcustomerflag = 0 would be the false

then the expression would be able to count the number of each?

Chart Help

vgutkovsky — Tue, 16 Nov 2010 21:53:21 GMT

Yep. Keep in mind though that the expression would be counting distinct account numbers, not the flags themselves. Set analysis works by simulating a selection. So you can think of it intuitively as first selecting NewCustomerFlag of 1 from an imaginary listbox and then counting the account #s associated with that value of the NewCustomerFlag.

Regards,

Chart Help

Tue, 16 Nov 2010 22:21:53 GMT

my apologies for being a pain but I am new to the scripting in QV

I am getting this error when running the script

Aggregation expressions not allowed in GROUP BY clause

if (count ([SAP Account #]) <= 1 ,1, 0) as Customer_Flag

Chart Help

vgutkovsky — Wed, 17 Nov 2010 00:42:15 GMT

Don't mean to question you, but are you sure that's the error you're getting verbatim? Because GROUP BY is the only place where you can aggregate. So your table load should look like this:

LOAD
[SAP Account #],
if(count(recordID)<=1,1,0) as NewCustFlag
RESIDENT data
GROUP BY [SAP Account #];

Note that it's better to count something other than what you're grouping by.

Regards,

Chart Help

Wed, 17 Nov 2010 14:40:55 GMT

I don't mind you questioning, because I am new to this so who knows what i am doing right.

I didn't have the Resident data or the Group by in my code.

I have tried the following syntax:

if (count ([SAP Account#])<=1,1,0) as Customer_Flag RESIDENT data GROUP BY [SAP Account #];

I have it placed after the LOAD (and all the items I am loading) from an excel sheet i am using for testing.

When I reload the script I get this message:

Aggregation expressions not allowed in GROUP BY clause

if (count ([SAP Account#])<=1,1,0) as Customer_Flag RESIDENT data GROUP BY [SAP Account #]

Chart Help

vgutkovsky — Wed, 17 Nov 2010 20:03:43 GMT

I suspect that's because you're not including the field you're aggregating by. So if your GROUP BY is over [SAP Account #] then you need to include [SAP Account #] in your load statement (without aggregation). The other fields in that load, however, must all be aggregated.

Regards,

Chart Help

Wed, 17 Nov 2010 20:15:59 GMT

again thank you for your help.

I was not aggregating everything, I was using the load command the software made from the excel sheet.

I have modified my script to basically just load the 1 thing I want to graph. It looks like this:

LOAD

[SAP Account #]

FROM

[grp-SAP Orders1.xlsx]

(ooxml, embedded labels);

if (count ([SAP Account#])<=1,1,0) as Customer_Flag RESIDENT data GROUP BY [SAP Account #];

That is all I have in the script (except for the set command for date time etc..) and I still get the same error.

Chart Help

vgutkovsky — Thu, 18 Nov 2010 04:56:53 GMT

Change it to this and it should work:


data:
LOAD
 [SAP Account #]
FROM
[grp-SAP Orders1.xlsx] (ooxml, embedded labels);
LOAD
 [SAP Account #],
 if (count ([SAP Account#])<=1,1,0) as Customer_Flag
RESIDENT data
GROUP BY [SAP Account #];

Meaning, this will run without errors. However, I would like to point out that it's better to be counting something other than what you're aggregating by.

Regards,