Show data for week,month,quarter,year based on dat... - Qlik Community

Report Inappropriate Content · ‎2015-01-20

I am trying to display data in pivot tables and charts based on only one date entered by user (requirement). I have created variable for date vInputDate and subsequent variables :

vMonth = Month(vInputDate)

vYear = Year(vInputDate)

vQuarter= not sure how to go about with this.

But when I try to use vMonth in expressions to show values for months, it shows no data to display in charts.

I am using SQL scripts to load the data into qlikview and not the Load statements ideally used.

Any help is much apprciated.

Anonymous · ‎2015-01-20

use this for QTR

Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,

kuba_michalik · ‎2015-01-20

Do you mean that those variables have to be used during the reload? Otherwise SQL or QlikView script shouldn't have any impact...

Anonymous · ‎2015-01-20

if you are using in expression then use like

=Dual('Q' & Ceil(Month(vInputDate)/3), Ceil(Month(vInputDate)/3))

you will get the qtr for selected date.

Report Inappropriate Content · ‎2015-01-20

No, those variables need not be used during the reload but in the subsequent expressions for the pivot table

kuba_michalik · ‎2015-01-20

In a scenario like this, using the variable within Set Analysis should do the trick, but to give you a more specific example I'd have to know what's the field containing the date, at least. If you have fields containing month, year and quarter in the data it would be best (if you don't have them, I'd suggest creating them during reload ), as the set analysis syntax would be simpler.

Report Inappropriate Content · ‎2015-01-20

I only have one Date field with value in the form MM/DD/YY 12:00:00 AM. I tried extracting values for month, year in the reload script itself but wasnt sure of how to tie my expressions and calculated values with respect to the date entered by user on the fly.

kuba_michalik · ‎2015-01-20

If your expression is for example Sum(somemeasure), and you have field in the data for the Month, then you could use set analysis like this:

Sum({<Month={'$(vMonth)'}>} somemeasure)

and it would show sum only across the records which have the same Month as the one coming from user-entered date.

If you don't want to create such a field, this:

Sum({<Date={"=Month(Date)=$(vMonth)"}>} somemeasure)

should work as well, but as you can see it's less nice and obvious

Report Inappropriate Content · ‎2015-01-20

this expression assigns the calculated quarter value for current date to all the dates in the table even if they dont belong to that particular quarter

Report Inappropriate Content · ‎2015-01-20

Sum({<Month={'$(vMonth)'}>} somemeasure) expression results in 0 value

Show data for week,month,quarter,year based on date entered by user.