help! How to create dashboard to show total number of customer by months

[Jaycole]

Dear All,

I am new to Qilk Sense 😉

Would to consult on how to create a nice and interactive dashboard using Qilk Desktop version. Please find attached excel file for your reference.

In the excel, please refer the table listing under tab "Data"; and refer to the expected dashboard outcome in Qilk Sense under tab "Expected Outcome in Qilk Sense ".

Requirement: To show the total number of customer by months.

Many thanks and appreciate!

Cole

[OmarBenSalem]

Here's a proposition:

1) In the script:

LOAD
Segment,
Category,
Area,
"Month",
"Total"
FROM [lib://data/Data.xlsx]
(ooxml, embedded labels, table is Data);

//Create the A segment

LOAD
'A' as Segment,
Category,
Area,
"Month",
"Total"
FROM [lib://data/Data.xlsx]
(ooxml, embedded labels, table is Data) where Segment='A1' or Segment='A2';

2) In the presentation:

a) Details table:

Pivot table:

As lines : Area, Segment

As columns: Month

as Measure: sum(total)

b) Composition %

Dimensions : Area, Segment

Measure: 

Sum(Total)/
sum({<Segment>}total <Area> Total)

c) Mom / YtD by Month

Dimension : Month

Measures:

MoM : 

aggr(

(Sum(Total)- above(Sum(Total)))
/
above(Sum(Total))

,Month)

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

YTD:

aggr(
(Sum(Total)- rangesum(above(Sum(Total),1,RowNo()) ))
/
rangesum(above(Sum(Total),1,RowNo()) )
,Month)

d) Mom by Segment:

Dimensions: Month, Segment

Measure: 

aggr(

(Sum(Total)- above(Sum(Total)))
/
above(Sum(Total))

,Segment, Month)

e) YTD by Segment 

Dimensions: Month, Segment

Measure: 

aggr(
(Sum(Total)- rangesum(above(Sum(Total),1,RowNo()) ))
/
rangesum(above(Sum(Total),1,RowNo()) )
,Segment, Month)

Result:

Now, thanks to the fact that all data is associated, if u want to track the behaviour of your Segment = A for example, just select it:

Hope you'll like it.

Omar BEN SALEM

[OmarBenSalem]

Here's a proposition:

1) In the script:

LOAD
Segment,
Category,
Area,
"Month",
"Total"
FROM [lib://data/Data.xlsx]
(ooxml, embedded labels, table is Data);

//Create the A segment

LOAD
'A' as Segment,
Category,
Area,
"Month",
"Total"
FROM [lib://data/Data.xlsx]
(ooxml, embedded labels, table is Data) where Segment='A1' or Segment='A2';

2) In the presentation:

a) Details table:

Pivot table:

As lines : Area, Segment

As columns: Month

as Measure: sum(total)

b) Composition %

Dimensions : Area, Segment

Measure: 

Sum(Total)/
sum({<Segment>}total <Area> Total)

c) Mom / YtD by Month

Dimension : Month

Measures:

MoM : 

aggr(

(Sum(Total)- above(Sum(Total)))
/
above(Sum(Total))

,Month)

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

YTD:

aggr(
(Sum(Total)- rangesum(above(Sum(Total),1,RowNo()) ))
/
rangesum(above(Sum(Total),1,RowNo()) )
,Month)

d) Mom by Segment:

Dimensions: Month, Segment

Measure: 

aggr(

(Sum(Total)- above(Sum(Total)))
/
above(Sum(Total))

,Segment, Month)

e) YTD by Segment 

Dimensions: Month, Segment

Measure: 

aggr(
(Sum(Total)- rangesum(above(Sum(Total),1,RowNo()) ))
/
rangesum(above(Sum(Total),1,RowNo()) )
,Segment, Month)

Result:

Now, thanks to the fact that all data is associated, if u want to track the behaviour of your Segment = A for example, just select it:

Hope you'll like it.

Omar BEN SALEM

[OmarBenSalem · Accepted solution]

here's a problem with this topic; I've already replied 2 times with A VERY LONG MESSAGE explaining each chart and the measures used within each one.

And both times, my message has been deleted...

Anyone would know.. why?

Here's the dashboard I've created as an example : 

[Jaycole]

Hi, can I have the qvf file for reference?

[Jaycole]

Hello, maybe you can try to repost the solution you've posted earlier? ("the long message")

[OmarBenSalem · Accepted solution]

:

1) In the script:

We create the Segment A:

LOAD
Segment,
Category,
Area,
"Month",
"Total"
FROM [lib://data/Data.xlsx]
(ooxml, embedded labels, table is Data);

LOAD
'A' as Segment,
Category,
Area,
"Month",
"Total"
FROM [lib://data/Data.xlsx]
(ooxml, embedded labels, table is Data) where Segment='A1' or Segment='A2';

2) In the presentation:

* Chart 1 : Composition %

- Dimension : Area, Segment

- Measure : Sum(Total)/sum({<Segment>}total <Area> Total)

* Chart 2 : MoM and YTD by Month

- Dimension: Month

- Measures: 

MoM Growht : aggr((Sum(Total)- above(Sum(Total)))/above(Sum(Total)),Month)

YTD Growth : aggr((Sum(Total)- rangesum(above(Sum(Total),1,RowNo()) ))/rangesum(above(Sum(Total),1,RowNo()) )
,Month)

* Chart 3 : MoM by Segment

- Dimension : Month, Segment

- Measure : 

aggr(

(Sum(Total)- above(Sum(Total)))
/
above(Sum(Total))

,Segment, Month)

* Chart 4 : YTD by Segment 

- Dimension: Month, Segment

- Measure : 

aggr(
(Sum(Total)- rangesum(above(Sum(Total),1,RowNo()) ))
/
rangesum(above(Sum(Total),1,RowNo()) )
,Segment, Month)

* Chart 5 : Details

Pivot table:

Lines: Area, Segment

Column : Month

Measure (put before column):

- sum : Sum(Total)

- MoM (%) : 

aggr(

(Sum(Total)- above(Sum(Total)))
/
above(Sum(Total))

,Segment, Month)

Ytd : 

aggr(
(Sum(Total)- rangesum(above(Sum(Total),1,RowNo()) ))
/
rangesum(above(Sum(Total),1,RowNo()) )
,Segment, Month)

Composition : Sum(Total)/
sum({<Segment>}total <Area> Total)

Result:

If u want to track only Segment = A, just select it:

Hope this won't be deleted...

[Jaycole]

Many thanks!!! will try it now ;D

[OmarBenSalem]

After all the effort put, u'll have to like my responses 😄 

[Jaycole]

Hello, for the above example, if i would like to filter by "Category" as well? example as below: