Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

elie_issa
Contributor II

Pivot Table measure issue

Dear All,

I am using the below expression in my pivot table :

Count({

< [Transfer In] = {'Yes'},

  [Joining Date]={"$(= '>=' & date($(vBOMFilterDate) + 1) & '<=' & date($(vEOMFilterDate)))"}

>

}Distinct [Assignment Number])

vBOMFilterDate :

=RangeMax(

Min(All{<Year=P(Year),Quarter=P(Quarter),[Month Year]=P([Month Year]),[Month Num]=P([Month Num]),Month=P(Month),Day=P(Day)>}Num([Master Calendar Date]))

,yearstart($(vEOMFilterDate)))

vEOMFilterDate:

= Max(All{<Year=P(Year),Quarter=P(Quarter),[Month Year]=P([Month Year]),[Month Num]=P([Month Num]),Month=P(Month),Day=P(Day)>}Num([Master Calendar Date]))

when opening the pivot table i am getting 0 for all years because the filters above will be :

vBOMFilterDate : 1/1/2018

vEOMFilterDate : 7/2/2018 (today's date)

when choosing a specific year we are getting the exact numbers.

What could be the solution in the pivot to make the measure working for all years even if we didn't choose any specific year.

Your urgent support is needed thank you.

ali_hijazi

5 Replies
ali_hijazi
Honored Contributor

Re: Pivot Table measure issue

so you mean you need to aggregate your expression by year

sum(aggr(count(......),Year))

elie_issa
Contributor II

Re: Pivot Table measure issue

I tried the below and still not working :

sum(aggr(

Count({

< [Transfer In] = {'Yes'},

  [Joining Date]={"$(= '>=' & date($(vBOMFilterDate) + 1) & '<=' & date($(vEOMFilterDate)))"}

>

}Distinct [Assignment Number],Year)))

I think the main issue is that the Dynamic dates are not changing correctly and always showing the latest year.

Any idea if we have other solution.

MVP
MVP

Re: Pivot Table measure issue

Would you be able to elaborate on what dimensions you have in the chart where you are using this?

elie_issa
Contributor II

Re: Pivot Table measure issue

it's not related to dimension because first i was trying to use it in a simple table with only the Year Dimension and still have wrong figures.

MVP
MVP

Re: Pivot Table measure issue

Not 100% sure, but try this

Sum({<[Transfer In] = {'Yes'}, [Joining Date]={"$(= '>=' & date($(vBOMFilterDate) + 1) & '<=' & date($(vEOMFilterDate)))"}>} Aggr(Count({<[Transfer In] = {'Yes'}, [Joining Date]={"$(= '>=' & date($(vBOMFilterDate) + 1) & '<=' & date($(vEOMFilterDate)))"}>} DISTINCT [Assignment Number],Year)))