Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Correlation Chart

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

   

InvoiceDateItem NameInvoice Amount
2/20/2015Water20.75
2/21/2015Water21.75
2/22/2015Water22.75
2/23/2015Water23.75
2/24/2015Water68.11
2/20/2015Bread390
2/21/2015Bread520
2/22/2015Bread520
2/23/2015Bread520
2/24/2015Bread880
2/20/2015Milk2614.8
2/21/2015Milk2614.8
2/22/2015Milk2614.8
2/23/2015Milk2614.8
2/24/2015Milk2614.8
2/20/2015Cheese3268.5
2/21/2015Cheese3922.2
2/22/2015Cheese5229.6
2/23/2015Cheese7844.4
2/24/2015Cheese785.38

Please help.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand

View solution in original post

10 Replies
Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
Not applicable
Author

@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.


talk is cheap, supply exceeds demand
Not applicable
Author

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
Not applicable
Author

@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.

Not applicable
Author

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

Not applicable
Author

Thank you so much for your help.

Gysbert_Wassenaar

Good points. You're absolutely right.


talk is cheap, supply exceeds demand
Gysbert_Wassenaar

See attached example.


talk is cheap, supply exceeds demand