Correlation Chart

[#1]

I have time series numeric data for all my sales items. I would like to correlate each item’s Invoice amount in that particular time period. I am able to correlate very easy in R (that may be because I am familiar with R

But cool thing with Qlik is ‘Interactive’ and I can pick and choose Water with Bread or Water with Milk or Milk with Cheese. In fact that is my requirement. My user want to pick and choose these items and would like to know correlation between those items.

Is this possible?

I found the similar solution Adrien Allard (130205_correlation.qvw) but not much luck with my data.

https://community.qlik.com/thread/84477

My sample Data

InvoiceDate	Item Name	Invoice Amount
2/20/2015	Water	20.75
2/21/2015	Water	21.75
2/22/2015	Water	22.75
2/23/2015	Water	23.75
2/24/2015	Water	68.11
2/20/2015	Bread	390
2/21/2015	Bread	520
2/22/2015	Bread	520
2/23/2015	Bread	520
2/24/2015	Bread	880
2/20/2015	Milk	2614.8
2/21/2015	Milk	2614.8
2/22/2015	Milk	2614.8
2/23/2015	Milk	2614.8
2/24/2015	Milk	2614.8
2/20/2015	Cheese	3268.5
2/21/2015	Cheese	3922.2
2/22/2015	Cheese	5229.6
2/23/2015	Cheese	7844.4
2/24/2015	Cheese	785.38

Please help.

[Gysbert_Wassenaar]

See attached qvw.

[#3]

@gwassenaar this is exactly what we are looking.

But we have to 500 K lines in our Databse. Is there a way we can load those records by command instead of writing all in edit script?

And is it possible take those records based on 'Customer name'? Example Customer 1 ...one set above records. Customer 2...other set. Basically Customer number is other column to above dataset.

Please let me know what you think?

[Gysbert_Wassenaar]

But we have to 500 K lines in our Databse. Is there a way we can load those records by command instead of writing all in edit script?

If you paid for a Qlikview training you should ask your money back. The INLINE is just one of many sources you can load data from. The result of an inline load is just a table, exactly as loads from other sources are. If your source data comes from a database then load the data from that database. If your source data comes from a text file then load from that text file. I don't have access to your database, so I loaded the data in my example with an inline load.

And is it possible take those records based on 'Customer name'?

Yes, as long as you have a customer name field in your data.

[#5]

My updated dataset will be like this...First I pickup a Customer --> Then I would like to see correlation. Or I won't pick up a customer, I would like to see correlation in entire my customer population. Basically correlation at two levels ...1) Entire customer population 2) At customer level

Thanks.

Customer,	InvoiceDate, Item Name, Invoice Amount
Hema,,	2/20/2015, Water, 20.75
Hema,	2/21/2015, Water, 21.75
Hema,	2/22/2015, Water, 22.75
Mur,	2/23/2015, Water, 23.75
Mur,	2/24/2015, Water, 68.11
Hema,	2/20/2015, Bread, 390
Hema,	2/21/2015, Bread, 520
Hema,	2/22/2015, Bread, 520
Mur,	2/23/2015, Bread, 520
Mur,	2/24/2015, Bread, 880
Hema,	2/20/2015, Milk, 2614.8
Hema,	2/21/2015, Milk, 2614.8
Hema,	2/22/2015, Milk, 2614.8
Mur,	2/23/2015, Milk, 2614.8
Mur,	2/24/2015, Milk, 2614.8
Hema,	2/20/2015, Cheese, 3268.5
Hema,	2/21/2015, Cheese, 3922.2
Hema,	2/22/2015, Cheese, 5229.6
Mur,	2/23/2015, Cheese, 7844.4
Mur,	2/24/2015, Cheese, 785.38

[#6]

@gwassenaar As an organization, we are spending lot of money in training. Qlik View trainers are coming to our campus and training groups of people. In fact we are spending millions of dollars for Qlik. Sometimes 2 or 4 day (30 hours) training only touch the surface. As an individual we all learn lot more while we start doing real time stuff (than examples given by trainer).

In my opinion, some of community posts are worth lot more than training and my sincere appreciation is extended to everybody in the Qlik community. Same time I agree that we need training for understanding basics. Again thanks for giving back and helping others.

[#7]

Qlikview offers integration with R so that you can do exactly what you are looking to do.

You can have your data loaded in Qlikview. Do whatever you want to filter the data. Provide list box that you can check "I want to pass R this field, this field and this field."

The following has to be done in Macro but it can be done:

1. Save the data to a CSV type file for R

2. Generate the script that R will read based on number of fields and which fields etc.

3. Shell out to R and have it execute.

4. Any output from R can be output to a file that can then be read back into Qlikview and displayed.

Brian Bauer is a Solutions Architect with Qlik and he did a wonderful job helping to get us up and going. He made a great post perhaps 2 years ago with detailed instructions on how to download and test the integration.

Here is a sample of the type of Macro code you could right. Notice it copies the object data to the clipboard and then builds the code to instruct R to read it's table from the clipboard. You have to build your R code within Qlikview macro but it's pretty straightforward and you can do things selectively.

ActiveDocument.GetSheetObject("CH26").CopyTableToClipboard true
mySelections=ActiveDocument.GetSheetObject("LB49").GetPossibleValues

'create a COM object representing R
Set R = CreateObject("StatConnectorSrv.StatConnector")
R.Init "R"

'Pass data to R from clipboard
R.EvaluateNoReturn "QVData <- read.table('clipboard')"
'Run kmeans R function with the selected data to find the cluster number
R.EvaluateNoReturn "fit <-kmeans(QVData,5)"
R.EvaluateNoReturn "aggregate(QVData,by=list(fit$cluster),FUN=mean)"
R.EvaluateNoReturn "QVData<-data.frame(QVData,fit$cluster)"
R.EvaluateNoReturn "ClusterResult<-t(QVData[4])"
R.EvaluateNoReturn "DisplayResult<-data.frame(ClusterResult)"
'R.EvaluateNoReturn "Result<-c(t(ClusterResult))"
'R.EvaluateNoReturn "DisplayResult<-data.frame(t(Result))"

'Write the data to text file for AUDIT purposes
R.EvaluateNoReturn "setwd('C:\\R\\')"
R.EvaluateNoReturn "write.table(QVData,'text.txt',col.names = NA)"

‌

brianbauer‌

[#8]

Thank you so much for your help.

[Gysbert_Wassenaar]

Good points. You're absolutely right.

[Gysbert_Wassenaar · Accepted solution]

See attached example.