Bar chart with zoom capability

[#1]

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

[MarcoWedel]

Hi,

you could use a drill down group to achieve this:

Hierarchic groups (drill-down)

hope this helps

regards

Marco

[MarcoWedel]

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

[#4]

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?

[#5]

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!!

[Anil_Babu_Samineni]

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

[#7]

Hi

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

[MK9885]

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.

[joseduque]

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

[#10]

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.