Re: how can "on click" changes month to week and ... - Qlik Community

msmSahaj · ‎2019-02-05

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-Month	Last Week	Last Month	Last Year
2014-12	no	no	yes
2014-12	no	no	yes
2014-12	no	no	yes
2014-12	no	no	yes
2014-11	no	no	yes
2014-11	no	no	yes
2014-11	no	no	yes
2014-11	no	no	yes
2014-11	no	no	yes
2014-11	no	no	yes
2014-11	no	no	yes
2014-11	no	no	yes
2014-11	no	no	yes
2014-11	no	no	yes
2014-10	no	no	yes
2014-10	yes	yes	yes
2014-10	no	no	yes
2014-10	no	yes	yes
2015-01	yes	yes	yes
2015-01	yes	yes	yes
2015-01	no	no	yes

Vegar · ‎2019-02-05

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.

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);

Vegar
Qlik Community MVP

msmSahaj · ‎2019-02-06

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 )

Thanks

Sahaj

msmSahaj · ‎2019-02-07

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.

Vegar · ‎2019-02-07

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.

Vegar
Qlik Community MVP

msmSahaj · ‎2019-02-07

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

Vegar · ‎2019-02-07

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

Vegar
Qlik Community MVP

msmSahaj · ‎2019-02-08

Hi @Vegar,

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 · ‎2019-02-08

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.

Vegar
Qlik Community MVP

msmSahaj · ‎2019-02-08

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

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

General Question