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: 
mohitd910
Creator II
Creator 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
JonnyPoole
Employee
Employee

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
Creator II
Creator II
Author

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

JonnyPoole
Employee
Employee


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
Creator II
Creator II
Author

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

JonnyPoole
Employee
Employee

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