topic Re: Advise on visualization in QlikView

Advise on visualization

Mon, 02 Jan 2017 17:54:10 GMT

Hi all, I have a table in the data model with the following fields:

Date Sales Discount Profit etc......

2016-12-01 9003 64 8104

2016-12-02 4902 311 4590

etc...

I want to create a visualization that has the following design:

(the user must select 1 week, for ex. Wk 50)

KPI Wk 49 Wk 50 % Inc

Sales 2415 5322 230%

Discount 642 344 53%

Profit

etc...

What is the best way to achieve this, ie having the expression on the left as a column?

Do you need to create an additional table in the Script?

Do you use pivot or straight table?

Thanks a lot for your help.

Jon

Re: Advise on visualization

vinieme12 — Mon, 02 Jan 2017 18:55:23 GMT

You need to do a crosstable load, refer the below link

The Crosstable Load

The load script for sample you provided will be like

Crosstable(KPI,Amount,1)

Load *

FROM YourSource;

Now Create a MasterCalendar for your date field , to have week, month,year,ytd of whatever flags you wish to visualize your data by

Better Calendar Scripts | Qlikview Cookbook

Re: Advise on visualization

Mon, 02 Jan 2017 19:04:00 GMT

Thanks Vineeth, Thanks for your suggestion. Is there another way to achieve the objective. If possible I would try to avoid the crosstable as it will complicate the data model, in order to have the considerable number of tables linked properly. Would something like this work, if create a table like the below in the data model, and use this table in a straight table? KPITable: Load Inline KPI Sales Discount Net Profit etc..

Re: Advise on visualization

vinieme12 — Mon, 02 Jan 2017 19:13:20 GMT

I think Crosstable will simplify things for you

Otherwise you will need to have separate expressions for Each KPI and checked to Horizontal

Whereas with cross table you can create a pivottable with Week and KPI as Dimension and Sum(Amount) as the ONLY Expression .

your choice, multiple expressions or single expression.

Re: Advise on visualization

Mon, 02 Jan 2017 19:30:02 GMT

Thanks Vineeth, Can you post a sample QVW of the above options if that is possible? I would like to try out both examples if you can assist me with that please? Thanks

Re: Advise on visualization

vinieme12 — Mon, 02 Jan 2017 19:58:53 GMT

See attached

Re: Advise on visualization

Mon, 02 Jan 2017 20:05:44 GMT

Lovely example Vineeth, Can you make the CrossTable work if you want for to show for ex. No of unique customers, or CrossTable work for sum only?

Re: Advise on visualization

vinieme12 — Mon, 02 Jan 2017 20:11:55 GMT

Crosstable is used to Normalize a Crosstabbed Pivot table, which is what your data is like

We've basically un-pivoted your data, so the resultant table is the IDEAL table to work with.

You can use Count(Distinct Customer) or whatever you want

Re: Advise on visualization

Mon, 02 Jan 2017 20:24:39 GMT

That's what I was thinking, but if you have just one expression sum(Amount) in the crosstable, can you please let me know how to user count( distinct amount ) or other types of aggregations for the respective kpi? Thanks for your patience

Re: Advise on visualization

vinieme12 — Mon, 02 Jan 2017 20:36:56 GMT

Can you upload some sample data and expected output, i'll try to recreate

Re: Advise on visualization

Mon, 02 Jan 2017 20:51:09 GMT

Yes of course.

I uploaded the Excel file with Data tab and Expected tab to give an idea of my expected layout.

Re: Advise on visualization

robert99 — Mon, 02 Jan 2017 20:54:41 GMT

you could try flippping it

KPI Wk 49 Wk 50 % Inc

Sales 2415 5322 230%

Discount 642 344 53%

Profit

etc...

ie Sales / Discount / Profit / %inc

wk49

wk50

with expression for every category (sales / discount etc)

and a dimension for weeks (in script)

SENSE

and then flip using a pivot table drag measure to the first column and weeks to the columns

View this can be done using a straight table

presentation --> Horizontal

Re: Advise on visualization

vinieme12 — Tue, 03 Jan 2017 06:30:21 GMT

Thanks, I'll check and get back to you

Re: Advise on visualization

robert99 — Tue, 03 Jan 2017 08:18:00 GMT

Maybe I missing the point ...

why don't you do it like this (as suggested above)

This is 15 minutes work. Getting the %var would be a bit longer. It could be done in script or in a chart

DATA:

LOAD

"Date",

WeekName(Date) as YrWeek , //

WeekNo,

text (CustomerID) as CustomerID,

Company,

Sales,

Discount,

Profit

FROM [lib://QlikSenseOther/CrosstabbingForum.xlsx]

(ooxml, embedded labels, table is Data);

and the output in Sense (the same can be done in View using a straight table. You could use your weekNum or as I have done)

but with measures renamed of course

to include the %inc something like this (or use peek)

DATA2:

Load

RowNo() AS ROW ,

YrWeek,

SUM (Sales) AS SalesWk ,

sum(Discount) as DiscountWk ,

sum(Profit) as ProfitWk

resident DATA

GROUP BY YrWeek

//ORDER BY YrWeek

;

data3:

Load

ROW,

SalesWk as Temp ,

previous (SalesWk) as SalesPrevWk ,

previous (DiscountWk) as DiscountPrevWk

resident DATA2 ;

//order by YrWeek ;

//====================================================

Or using peek (NB peek based on output / previous based on input)

DATA2:

Load

RowNo() AS ROW ,

YrWeek,

SUM (Sales) AS SalesWk ,

sum(Discount) as DiscountWk ,

sum(Profit) as ProfitWk ,

peek ('SalesWk', -1,'DATA2') as SalesPrevWk //etc likewise discount and profit

resident DATA

GROUP BY YrWeek

ORDER BY YrWeek

;

Re: Advise on visualization

robert99 — Tue, 03 Jan 2017 08:25:36 GMT

and if you want a more complex but slick way of doing it look at thsi thread

How do I insert a calculated row in a straight table ?

Re: Advise on visualization

robert99 — Tue, 03 Jan 2017 08:58:49 GMT

I also had a go with the cross-table approach

Its an alternative way but I would just use the the 4 expressions

If it was me I would show them the non cross-table script load and straight table format

but with this the Pivot Table looks tidier (although with total turned off). But if presentation was key maybe look at the above post

DATA:

LOAD

Dual('Sales',1) as Type ,

"Date",

WeekName(Date) as YrWeek ,

WeekNo,

text (CustomerID) as CustomerID,

Company,

Sales as Value

// Discount,

// Profit

FROM [lib://QlikSenseOther/CrosstabbingForum.xlsx]

(ooxml, embedded labels, table is Data);

DATA:

concatenate (DATA)

LOAD

dual ('Discount',2) as Type ,

"Date",

WeekName(Date) as YrWeek ,

WeekNo,

text (CustomerID) as CustomerID,

Company,

// Sales,

Discount as Value

// Profit

FROM [lib://QlikSenseOther/CrosstabbingForum.xlsx]

(ooxml, embedded labels, table is Data);

DATA:

Concatenate (DATA)

LOAD

dual ('Profit',3) as Type ,

"Date",

WeekName(Date) as YrWeek ,

WeekNo,

text (CustomerID) as CustomerID,

Company,

// Sales,

// Discount as Value

Profit as Value

FROM [lib://QlikSenseOther/CrosstabbingForum.xlsx]

(ooxml, embedded labels, table is Data);

Re: Advise on visualization

pathiqvd — Tue, 03 Jan 2017 09:21:36 GMT

Hi,

you can try with value list you will get,

add calculated dim:- valuelist(Unique Customer,sales,discount,Profit)

expr's:-

1) for wk49:-

if(valuelist(Unique Customer,sales,discount,Profit)='Unique Customer','write hereucs expr',

if(valuelist(Unique Customer,sales,discount,Profit)='sales','sales expr',

if(valuelist(Unique Customer,sales,discount,Profit)='discount','discountexpr',etc)))

2) for wk50:-

if(valuelist(Unique Customer,sales,discount,Profit)='Unique Customer','write hereucs expr',

if(valuelist(Unique Customer,sales,discount,Profit)='sales','sales expr',

if(valuelist(Unique Customer,sales,discount,Profit)='discount','discountexpr',etc)))

3) % Inc:-

if(valuelist(Unique Customer,sales,discount,Profit)='Unique Customer','write hereucs expr',

if(valuelist(Unique Customer,sales,discount,Profit)='sales','sales expr',

if(valuelist(Unique Customer,sales,discount,Profit)='discount','discountexpr',etc)))

....like

Regards,

Re: Advise on visualization

vinieme12 — Tue, 03 Jan 2017 19:12:39 GMT

For your expected output it's not like we could have expressions to aggregate against dimensions , because you want KPI's also the headers are a mix of Period(week) and % and a custom period (LY week) so we are better off with a straight table

attaching sample app for reference.