Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Bar chart with zoom capability

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

19 Replies
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:

QlikCommunity_Thread_244283_Pic1.JPG

QlikCommunity_Thread_244283_Pic2.JPG

QlikCommunity_Thread_244283_Pic3.JPG

QlikCommunity_Thread_244283_Pic4.JPG

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

Not applicable
Author

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?

Not applicable
Author

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Not applicable
Author

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
Master II
Master II

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
Partner - Contributor III
Partner - Contributor III

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

Not applicable
Author

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.