topic Re: Bar chart with zoom capability in QlikView

Bar chart with zoom capability

Wed, 25 Nov 2020 16:16:04 GMT

Hi I have a sales data table that has monthly sales amount projections for next 20 years. i like to create a bar chart that displays the sales data by year first, but if you zoom then it shows by quarter and upon further zoom by month. is this doable in qlik, sorry i am new.

sales	month/year
456,736.00	9/16
4,432,345.00	10/16
3,432,344.00	11/16
3,344,234.00	12/16
433,334.00	1/17
2,223,432.00	2/17

thanks nikit

Re: Bar chart with zoom capability

MarcoWedel — Fri, 23 Dec 2016 00:16:05 GMT

Hi,

you could use a drill down group to achieve this:

Hierarchic groups (drill-down)

hope this helps

regards

Marco

Re: Bar chart with zoom capability

MarcoWedel — Fri, 23 Dec 2016 00:31:40 GMT

maybe like this:

LOAD *,
    Month([month/year]) as Month,
    'Q'&Ceil(Month([month/year])/3) as Quarter,
    Year([month/year]) as Year;
LOAD sales,
    Date#([month/year],'MM/YY') as [month/year]
FROM [https://community.qlik.com/thread/244283] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: Bar chart with zoom capability

Fri, 23 Dec 2016 02:28:54 GMT

Thank you so much Marco, it worked using a spreadsheet file but I am not able to translate the syntax using a SQL query. The following is not working, could you please help me with the syntax?

LOAD *, Month([cash_flow_date]) as Month,
'Q'&Ceil(Month([cash_flow_date])/3) as Quarter,
Year([cash_flow_date]) as Year;

select company,
Product,
cash_flow_date as cash_flow_date,
Sales
from financial.Sales_rec;
Also, does the cash_flow_date's data_type has to be a "date" or varchar2 is ok?

Re: Bar chart with zoom capability

Fri, 23 Dec 2016 02:39:21 GMT

Hi Marco

Thank you so much it works with spreadsheet data but I am having issues with syntax using it in a SQL query:

LOAD *,
Month([cash_flow_date]) as Month,
'Q'&Ceil(Month([cash_flow_date])/3) as Quarter,
Year([cash_flow_date]) as Year;

select company,
Product,
cash_flow_date as cash_flow_date,
Sales
from Sales_rec;

Could you please help me with the syntax for using in SQL statement? Also does the cash_flow_date field have to be a "Date" data_type?

Thanks again!!

Re: Bar chart with zoom capability

Anil_Babu_Samineni — Fri, 23 Dec 2016 04:42:19 GMT

It should work, Have you created group for them with the pattern of (Year -- Quarter -- Month)

And then use dimension as same (Cyclic Group / Drill down)

Then, use simply Sum(Sales) and then check whether it is spreading or not

Re: Bar chart with zoom capability

Fri, 23 Dec 2016 14:26:02 GMT

Actually there is a Script error:

ErrorSource: OraOLEDB, ErrorMsg: ORA-00936: missing expression

select company,

Product,

Date#([cash_flow_date],'MM/YYYY') as [cash_flow_date],

liability_cash_flow

from financial_risk.nikit_alm_tool

I don't think the syntax is correct in a SQL query so while executing the query it is generating an error.

Thanks nikit

Re: Bar chart with zoom capability

MK9885 — Fri, 23 Dec 2016 16:36:37 GMT

I'd suggest extract the data from your db as it is.

Save the file into QVD.

Load the QVD and do the transformations there instead.

The error might be due to missing syntax like , or ; but everything seems fine or the columns names are not matching.

I do not know much about this.

But Follow the procedure of ETL

Extract the data from db as it is, save in qvd.

Use that qvd to make any transformations like changing names or date functions

And finally load.. hopefully that should work.

Re: Bar chart with zoom capability

joseduque — Fri, 23 Dec 2016 16:54:00 GMT

Hi Nikit,

Maybe The cash_flow_date field you have to convert,Example something like:

convert(char(10),cash_flow_date,111) as cash_flow_date

Then save this table in a qvd. Is much easier to use a qvd and then Apply the functions

Month(cash_flow_date) as Month,

Year(cash_flow_date) as Year,

'Q'&Ceil(Month(cash_flow_date)/3) as Quarter

Re: Bar chart with zoom capability

Tue, 27 Dec 2016 18:52:54 GMT

Thank you. Its working with QVD but its not letting me do what I intended it to do. As you can see from the first screen shot below, I an selecting a subset using the mouse but its not zooming-in to "Quarters" which is what I want. if I click on one of the blue bars it shows "Quarter" results but I need it so show "Quarters" for all years when I select a subset. Any thoughts would be greatly appreciated.

Re: Bar chart with zoom capability

MK9885 — Tue, 03 Jan 2017 14:26:26 GMT

As far as I know you cannot have a drill down selection to Quarters if you select multiple years.

Cus when you select more than 1 year at a time then it will show you data for 4 quarters (Q1,Q2,Q3,Q4).

And if you want to see data for only 2 or 3 quarters then you've to do it through Quarter Filter.

Select more than 1 year and then select the desired Quarter you want to see.

Re: Bar chart with zoom capability

sunny_talwar — Tue, 03 Jan 2017 14:39:56 GMT

May be it can be done using conditional dimensions? If a value is selected in Year field, Year dimension goes away and Quarter is activated and so on....

Re: Bar chart with zoom capability

sunny_talwar — Tue, 03 Jan 2017 14:47:31 GMT

Like the attached

Re: Bar chart with zoom capability

MK9885 — Tue, 03 Jan 2017 14:49:04 GMT

I've tried at my own qvw using if(getselectedcount(year)>=1,.....

and also tried with conditional expression

if(getcurrentselection(year)....

and it doesn't seem to work with multiple years. Like if I select 2011 and 2012 at same time then it shows only year but not break it down into all 8 (2011, 2012) quarters.

If I understood his question correctly, this is what he is seeking? Not an expert but I don't know.

Re: Bar chart with zoom capability

sunny_talwar — Tue, 03 Jan 2017 14:53:13 GMT

I might have missed the requirement, but can you check the qvw I have attached above?

Re: Bar chart with zoom capability

MK9885 — Tue, 03 Jan 2017 14:55:54 GMT

Oh yes!!

As always you know how to do it.

Thanks, I learned something new today.

I hope nikitmehta will get his answer in your sample qvw.

Re: Bar chart with zoom capability

MK9885 — Tue, 03 Jan 2017 14:58:59 GMT

But will it also work if we using the Year,Quarter and Month as Groups?

If not then maybe he has to add individual dimensions as you did.

Re: Bar chart with zoom capability

sunny_talwar — Tue, 03 Jan 2017 14:59:21 GMT

The only thing which might be more helpful here would be to use QuarterYear and MonthYear instead of Quarter and Month because selecting multiple years will group all those Quarters into one... So may be like this....

Re: Bar chart with zoom capability

sunny_talwar — Tue, 03 Jan 2017 15:00:37 GMT

Drilldown group won't work (just as you pointed out) and the op will need to use multiple dimensions with hide/show

Re: Bar chart with zoom capability

MK9885 — Tue, 03 Jan 2017 15:01:35 GMT

Oh ok got it.

It was helpful for me with both the examples you've provided.

Thanks.