Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
msmSahaj
Contributor III
Contributor III

how can "on click" changes month to week and week to days in qlik sense on X axis

 

Hi Experts,

Currently i have Chart based on "Year-month" column on X axis.  I need to calculated on click base on below columns(Last Week, Last Month, Last year)

       1. If chart x axis is year then by click on year should display all month.

      2. If chart x axis is month then by click any month should display weeks of that month(min 4 weeks if  3 week completed  month on this month, then previous month last week)

    3. if chart x axis is week then click on any week should display all 7 day. 

 

 

Year-MonthLast WeekLast MonthLast Year
2014-12nonoyes
2014-12nonoyes
2014-12nonoyes
2014-12nonoyes
2014-11nonoyes
2014-11nonoyes
2014-11nonoyes
2014-11nonoyes
2014-11nonoyes
2014-11nonoyes
2014-11nonoyes
2014-11nonoyes
2014-11nonoyes
2014-11nonoyes
2014-10nonoyes
2014-10yesyesyes
2014-10nonoyes
2014-10noyesyes
2015-01yesyesyes
2015-01yesyesyes
2015-01nonoyes
 
 Please help with this...
Thanks,
sahaj 
Labels (1)
14 Replies
Vegar
MVP
MVP

I recommend you to create some kind of date/period field that has the granularity you are looking for. It is hard to plot weekdays when your most granular period is month. 

When you have done that you could create hiearchical dimentions that does the drill down functionality that you are searching for. 

Create a hieararchic dimension. The language might be strange, but the layout should be similar in your environment.Create a hieararchic dimension. The language might be strange, but the layout should be similar in your environment.

The script below was used to create year and month from your data table.

Load
Month(%Period) as Month,
Year(%Period) as Year,
*;
LOAD
	date#([Year-Month],'YYYY-MM' ) as %Period,
	[Last Week],
	[Last Month],
	[Last Year]
 FROM [lib://Calendarsource]
(html, codepage is 1252, embedded labels, table is @1);


 

msmSahaj
Contributor III
Contributor III
Author

Hi Vegar, 

Thanks you so much for help, 

I have follow you step change my loading  script to data table.

1.The issue is not we deep down to period it is displaying "yes, no" how can we change to week, suppose below img is 11/1/18 to 12/1/18 now its should display in other chart as  November month of 4 week only?

for example : 11/4, 11/11, 11/18, 11/25 in  X axis ? now its displaying yes, no? 

2.other question i need to do same thing with days (if you choose 11/4 week then display then display 7 day from 11/4 to 11/10 in X axis )

 

 

period 1.PNGperiod.PNGperiod3.PNG

Thanks

Sahaj

msmSahaj
Contributor III
Contributor III
Author

Hi @Vegar Thank you so much for all help. I have found out.

 

I have question reference to above, I have another filed will Timestamp(see below). How do i created drill down of  year, month, week, days.

Please help with this Vegar, thanks in advance.

Capture.PNG

Vegar
MVP
MVP

In your transaction table where you have the timestamp create a new field called %date like the script below.

Transactions:
LOAD
Dimension1, 
Dimension2,
Timestamp, 
date(SubField(Timestamp, ' ',1)) as %date 
FROM YourSource;

Then, after you created this field, run the code seen below to create an master calendar.

for each _date in FieldValueList ('%date')
  MasterCalendar:
LOAD '$(_date)' as %date, year('$(_date)') as Year, month('$(_date)') as Month, Week('$(_date)') as Week, Weekday('$(_date)') as Weekday AutoGenerate 1; next

You can expand the fields in this calendar according to your own need ant taste. (Google search for  master calendar Qlik to find other more detailed master calendars.

Take a look at my earlier comment on how to create drill down dimensions. Be aware when using week in drill down from month, a week can belong to two months. 

 

 

msmSahaj
Contributor III
Contributor III
Author

Hi @Vegar , thanks a lot for reply,  I am seeing below result for you answer.  There is no x axis times stamp.C_2.PNGC.PNG

Vegar
MVP
MVP

If you want the timestamp you should keep it in your transaction table, as I did in my example.
msmSahaj
Contributor III
Contributor III
Author

Hi @Vegar,

       2.PNG1.PNG

I have Follow you step and got it, But now when i click on drill down from month to week its shows above as number 48 52.  How can we display weeks of December only (1 to 4)

Thanks for your help.

sahaj

Vegar
MVP
MVP

This is QlikView screenshot, but i will be similar in Qlik Sense. The Dim1, Dim2 and Etc will of cource be replaced with your field names. 

image.png

msmSahaj
Contributor III
Contributor III
Author

Hi @Vegar My model is look like this now, is it right?

1.PNG