Qlik Community

Qlik Sense Integration, Extensions, & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
mohitd910
Contributor II

how to use expression for dimension and measure in QilkSense Bar chart

I am working on QlikSense and having one table with two columns Date and Stock. I just need to show barchart to show previous three months stock using stock date like current month month0, previous month month1 and rest all in month2.

StockDate       Stock

08/02/2014      100

07/02/2014      200

08/02/2015      100

07/02/2015      100

06/02/2015      100

These value we need to change on Date selection also i.e if someone select Aug 2014 then current month is Aug 2014.

I am using Expression in dimension to make Month0, Month1 etc but facing problem to use same expression for measure (it's not returning correct data).

So can anyone help how to map measure part with dimension if we are making dimension using expression?

Many Thanks

5 Replies
Employee
Employee

Re: how to use expression for dimension and measure in QilkSense Bar chart

If you define a variable in the script (or as of 2.1 in the UI) with one of the following:

script:   set vMaxSelectedDate=date(max(StockDate));

UI:       vMaxSelectedDate     =date(max(StockDate))

This expression in a chart will filter the data to just the last 3 months

Sum( {<StockDate={">=$(=date(monthstart(addmonths(vMaxSelectedDate,-2))))<=$(=date(monthend(vMaxSelectedDate)))"}>} Stock)

Attached is a sample with the data you provided

mohitd910
Contributor II

Re: how to use expression for dimension and measure in QilkSense Bar chart

Thanks Sir but UI Doesn't Works.

Sir i need a help i m using max selected date for current month  i want to calculate Stock For same current month till today date but last year how it is possible please help

Like Unallocated  Stock for current month to till date

Previous month all stock

now i want to calculate for previous year current month till date

Untitled.png

Employee
Employee

Re: how to use expression for dimension and measure in QilkSense Bar chart


Same approach, just modify the date range in the Set Analysis modifier using the variable defined in the previous post:



Current Month to date

Sum( {<StockDate={">=$(=date(monthstart(vMaxSelectedDate)))<=$(=date(vMaxSelectedDate))"}>} Stock)

Last 3 months (up to today)

Sum( {<StockDate={">=$(=date(monthstart(addmonths(vMaxSelectedDate,-2))))<=$(=date(vMaxSelectedDate))"}>} Stock)

Last 3 months (previous year)

Sum( {<StockDate={">=$(=date(monthstart(addmonths(vMaxSelectedDate,-14))))<=$(=date(addmonths(vMaxSelectedDate,-12)))"}>} Stock)

Previous Month:

Sum( {<StockDate={">=$(=date(monthstart(addmonths(vMaxSelectedDate,-1))))<=$(=date(monthend(addmonths(vMaxSelectedDate,-1))))"}>} Stock)

mohitd910
Contributor II

Re: how to use expression for dimension and measure in QilkSense Bar chart

We need to filter on date also for previous year like as today's date is 22 sep, so we need to get data from 1st to 22nd for previous year..

set vMaxSelectedDate=date(max(StockDate));

SUM({<Year =, Month =, Week = , StockDate = {">=$(=MonthStart(AddYears($(vMaxSelectedDate),-1),0))<=$(=MonthEnd(AddYears($(vMaxSelectedDate),-1),0))"}>}(TotalUnAllocatedVal))

Employee
Employee

Re: how to use expression for dimension and measure in QilkSense Bar chart

As of today's date is different to as of the user's max selected date. Replace vMaxSelectedDate with Today() function. 

Previous year month to date (as of today) would be:

SUM({<Year =, Month =, Week = , StockDate = {">=$(=MonthStart(AddYears(today(),-13)))<=$(=MonthEnd(AddYears(today(),-13)))"}>}(TotalUnAllocatedVal))