Sum of distinct counts based on dates selection

[#1]

Hi,

I need to calculate the sum of distinct counts of a variable( See the below sample data). this is a pivot data, but original data is based on the dates for more records based on a unique id.For the current month, I'm getting the sum based on the below syntax. When I again write the same syntax to get the previous month i.e. max of current month - 1 = Previous month, then also my sum is giving me the current month sum of distinct counts instead of last month counts.

SUM OF DISTINCT COUNTS FOR CURRENT MONTH:

NEW_MEND_DATE = Date(MonthStart([MEND_DT]), 'MMM-YYYY') as NEW_MEND_DATE

vmin_mendate =Min(Date([NEW_MEND_DATE], 'MMM-YYYY'))

= sum(Aggr(count((WRITE_OFF)),NEW_MEND_DATE={">=$(vmin_mendate)=$(vmax_mendate))"}))

SUM OF DISTINCT COUNTS FOR PREVIOUS MONTH:

NEW_MEND_DATE = Date(MonthStart([MEND_DT]), 'MMM-YYYY') as NEW_MEND_DATE

vmin_mendate =Min(Date([NEW_MEND_DATE], 'MMM-YYYY'))

vmax_mendate_prev = =Date(Max([NEW_MEND_DATE]) - 1, 'MMM-YYYY')

= sum(Aggr(count((WRITE_OFF)),NEW_MEND_DATE={">=$(vmin_mendate)=$(vmax_mendate_prev))"}))

DATE FORMAT OF NEW_MEND_DATE AND MY VARIABLE

[sunny_talwar · Accepted solution]

Are you sure this is working?

Doesn't look like the correct syntax.... are you may be looking for thie

CURRENT MONTH

Count({<NEW_MEND_DATE = {"$(=Date(Max(NEW_MEND_DATE), 'MMM-YYYY'))"}>}WRITE_OFF)

PREVIOUS MONTH

Count({<NEW_MEND_DATE = {"$(=Date(MonthStart(Max(NEW_MEND_DATE), -1), 'MMM-YYYY'))"}>}WRITE_OFF)

[#3]

hi Sunny,

yes I did my syntax as below and was working fine. Thanks for sending it to me.

=(count({<NEW_MEND_DATE={">=$(vmin_mendate) <=$(vmax_mendate)"}>} WRITE_OFF)
-
count({<NEW_MEND_DATE={">=$(vmin_mendate) <=$(vmax_mendate_PREV)"}>} WRITE_OFF)
)

Thanks

Sowjanya

[#4]

Hi Sunny,

Can you also help me with the below please.

Can we create a TREND LINE / CHART in a column based on month data into a pivot table? Below is the sample data. In column G I want to add a trend line showing the performance of the actual vs target. please advise if this is possible?

thanks

Sowjanya

[sunny_talwar]

For each Dummy variable you want to construct a trend line?

[#6]

Yes. For each row  I need to add trend line for all the months

for eg.

c-4 , c10, c13 ---------------for all the 4 months I need to see the performance of actual in form of trend line graph.

Something like the below.

[sunny_talwar]

Check this out

http://buffalobi.com/qlikview/qlikview-mini-chart/