Pie chart % selection should reflect the Transactions in straight table

[hariprasad_koya]

Hi friends,

Can anyone suggest any idea on this...I tried a lot of ways but not able to work it out.The below query should be done at UI level itself.

I just have one query. Though it is loking very simple but iam not able to achieve it. so require help.

I have the salesorder date and sales data sample sheet. only below 2 points to be shown in qlikview

1. Pie chart showing % of Sales <30 days,31-60,61-90,>90 days wrto total sales.(it was created in the QVW)

2.Upon the selection of pie chart slices respective data should be shown in the straight table(Salesorderdate and Sales)

Eg : if i select <30 days slice in pie chart it should show straight table for <30 days transactions.

Please help me out regarding this.Please find the  attachments.

Regards,

Hari

[hariprasad_koya]

Hi Anil,

No not working...Data is there i have created sample data for every period.

Thanks,

Hari

[sasikanth]

HI hari,

try below expression as calculated dim in pie chart

=if( num(Today())-num([Sales Date])<30,'<30',

if(num(Today())-num([Sales Date])>=30  and num(Today())-num([Sales Date])<=60 , '30 to 60',

If(num(Today())-num([Sales Date])>60 and num(Today())-num([Sales Date])<=90,'61 to 90',

If(num(Today())-num([Sales Date])>90,'>90'))))

use the same expression as calculated dimension in your  straight table  along with date dimension

Table

Dim1: sales Date

Dim 2: range (calculated dim)

exp : sum(sales)      ( enable relatives option to get % figures)

PFA, for you reference

[Anil_Babu_Samineni]

I have access ur file noe, Tell me how i can offer you

[hariprasad_koya]

Hi Sasi,

i just have one more query...if u can help me out..Below is the query.

1. First chart using valuelist and data wise is correct, but i cant select the % slices in pie chart which should reflect the transactions in the straight table.

2. I have designed the same chart using calculated dimension but the the data is not coming accurately for <=30 days,31-60 days,61-90 days,>90 days.

I have done the changes as you have suggested in the above thread.

Request you to help.

regards,

Hari

[sasikanth]

Hi Hari,

You can the below exp as calculated dimension

=IF($(vNew)- Num([Sales Date])<30,'<30',

IF($(vNew)- Num([Sales Date])>=30 and $(vNew)- Num([Sales Date])<=60,'30 to 60',

IF($(vNew)- Num([Sales Date])>60 and $(vNew)- Num([Sales Date])<=90 ,' 61 to 90','>90')))

where vNew =num(max([Sales Date]))

In your chart '31 to 60' range is wrong because first bucket value is  <30 means we missed 30th day related sales.

hope it will help you to solve the problem

[hariprasad_koya]

Hi Sasi,

Still it is not working...Calculated dimesion error.

Please let me know how to work it out.

Thanks,

Hari

[arvind1494]

Dimension :

=If(Day([Sales Date]) <30, Dual('<30 days',1),

If((Day([Sales Date]) >30 and Day([Sales Date]) <= 60), Dual('31-60',2),

If((Day([Sales Date]) >60 and Day([Sales Date]) <= 90), Dual('61-90',3),

Dual('>90 days',4))))

Expression :

sum(Sales)

[karthikoffi27se]

Hi Hari,

Try this

If(Day([Sales Date]) <30, '<30 days',

If(Day([Sales Date]) >30 and Day([Sales Date]) <= 60, '31-60',

If(Day([Sales Date]) >60 and Day([Sales Date]) <= 90, '61-90', '>90 days'))) as Days_Bucket

This will work.

Many Thanks

Karthik

[hariprasad_koya]

Hi arvind,

Not working...Can you pls suggest some other idea.

Regards,

Hari

[hariprasad_koya]

Hi Karthik,

Not working...Can you pls suggest some other idea.

Regards,

Hari