topic Re: Expression that assumes a selection is made in QlikView

Expression that assumes a selection is made

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have an expression below

Previous month: Count({<[Calendar Year/Month] ={'$(vYMLess1)'}, Month= >} Distinct [Business Partner Parent ID]))

Current month: Count({<[Calendar Year/Month] ={'$(vYM)'}, Month= >} Distinct [Business Partner Parent ID]))

where vYMless1 relates to the previous month and vYM is the current month

so for eg , if vYM = 201706 , then vYMLess1 = 201705

and Month will be June and May respectively.

The issue I'm having is with the formula relating to the previous month. It only works if I select June and May. If i only select June, then the previous months formula gives an incorrect answer.

So basically, what i need is to create an expression for the previous month that assumes that a selection is made for the current month and previous month.

Please can you assist. I hope I have conveyed my message clearly.

Kind regards

Nayan

Re: Expression that assumes a selection is made

Anil_Babu_Samineni — Thu, 06 Jul 2017 07:22:08 GMT

If you are selecting Month Field, Could be the reason you are not selecting Year. Can you try selection happen from [Calendar Year/Month] field and then check whether it comes or not. If not, I would suggest you to share sample application to work

Re: Expression that assumes a selection is made

kaushiknsolanki — Thu, 06 Jul 2017 07:25:34 GMT

Hi,

Can you tell us what expression you have written in both variables.

Regards,

Kaushik Solanki

Re: Expression that assumes a selection is made

Anonymous — Thu, 06 Jul 2017 07:36:46 GMT

If Year is selected, there is no change.

Actually the formula given above is a simplistic information. My actual formula is as follows which is an aggregation.

Unfortunately i cannot share the model because of sensitivity of information.

Kind regards

Nayan

sum(aggr(

( Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

[Sales Org] = {'RU01'},

[Target Distribution Channel] = {'31','32'},

[Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

[Terrotory Cluster] -= {'05'},

Counter = {'1'},

[Trade Channel Name] = {'Traditional Trade'},

SegmentationMapped={'Gold','Silver', 'Gold+'},

[Territory Cluster] -= {'Autonom. Distr'},

Country = {'Russia'},

Month

Distinct [Business Partner Parent ID])

Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

[Sales Org] -= {'RU01'},

[Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

[Terrotory Cluster] -= {'05'},

Counter = {'1'},

[Trade Channel Name] = {'Traditional Trade'},

SegmentationMapped={'Gold','Silver', 'Gold+'},

[Territory Cluster] -= {'Autonom. Distr'},

Country = {'Russia'},

Month

Distinct [Business Partner Parent ID]))

, Outlet))

Re: Expression that assumes a selection is made

Anonymous — Thu, 06 Jul 2017 07:38:57 GMT

Hi Kaushik

As requested.

vYM

=max([Calendar Year/Month])

vYMLess1 =Year(Date(AddMonths(Makedate(left(Trim($(vYM)),4),Right(Trim($(vYM)),2),'01'),-1)))&Num(Month(Date(AddMonths(Makedate(left(Trim($(vYM)),4),Right(Trim($(vYM)),2),'01'),-1))),'00')

Kind regards

Nayan

Re: Expression that assumes a selection is made

kaushiknsolanki — Thu, 06 Jul 2017 07:43:00 GMT

Hi,

First of all check of the vYMLess1 is taking any value when you dont select any month?

If yes then you need to check the expression, else you should focus on variable.

I would write it in other way like below.

vYMLess1

=Date(Addmonths(Date(Date#($(vYM),'YYYYMM')),-1,'YYYYMM')

Regards,

Kaushik Solanki

Re: Expression that assumes a selection is made

Anonymous — Thu, 06 Jul 2017 08:09:49 GMT

Hi Kaushik

If i don't select any month, the vYMLess1 is showing correct values. Thank you for your reply.

Not sure if you seen my expression when i replied to Anil.

Kind regards

Nayan

Re: Expression that assumes a selection is made

kaushiknsolanki — Thu, 06 Jul 2017 08:34:47 GMT

I can see that you have missed the '=' for the Month Field.

Your expression should be.

sum(aggr(

( Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

[Sales Org] = {'RU01'},

[Target Distribution Channel] = {'31','32'},

[Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

[Terrotory Cluster] -= {'05'},

Counter = {'1'},

[Trade Channel Name] = {'Traditional Trade'},

SegmentationMapped={'Gold','Silver', 'Gold+'},

[Territory Cluster] -= {'Autonom. Distr'},

Country = {'Russia'},

Month=

Distinct [Business Partner Parent ID])

Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

[Sales Org] -= {'RU01'},

[Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

[Terrotory Cluster] -= {'05'},

Counter = {'1'},

[Trade Channel Name] = {'Traditional Trade'},

SegmentationMapped={'Gold','Silver', 'Gold+'},

[Territory Cluster] -= {'Autonom. Distr'},

Country = {'Russia'},

Month=

Distinct [Business Partner Parent ID]))

, Outlet))

Regards,

Kaushik Solanki

Re: Expression that assumes a selection is made

Anonymous — Thu, 06 Jul 2017 10:17:31 GMT

Hi Kaushik

I have added in the "=" sign, however no difference. Does it make a difference to put an "=".

Kind regards

Nayan

Re: Expression that assumes a selection is made

kaushiknsolanki — Thu, 06 Jul 2017 10:20:16 GMT

Hi,

I feel it should work.

Only way to look more into is to see actual data.

Please share the sample application.

Regards,

Kaushik Solanki

Re: Expression that assumes a selection is made

Anonymous — Thu, 06 Jul 2017 10:49:17 GMT

Hi Kaushik

Thank you for your reply. What i try to do is a mock QV Application and see if i can upload the model.

Kind regards

Nayan

Re: Expression that assumes a selection is made

kaushiknsolanki — Thu, 06 Jul 2017 10:50:14 GMT

Sure.

Regards,

Kaushik Solanki

Re: Expression that assumes a selection is made

sunny_talwar — Thu, 06 Jul 2017 11:22:16 GMT

I think you need set analysis for your outer aggregation here

Sum({<[Calendar Year/Month] ={'$(vYMLess1)'}>} Aggr(

( Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

[Sales Org] = {'RU01'},

[Target Distribution Channel] = {'31','32'},

[Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

[Terrotory Cluster] -= {'05'},

Counter = {'1'},

[Trade Channel Name] = {'Traditional Trade'},

SegmentationMapped={'Gold','Silver', 'Gold+'},

[Territory Cluster] -= {'Autonom. Distr'},

Country = {'Russia'},

Month

Distinct [Business Partner Parent ID])

Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

[Sales Org] -= {'RU01'},

[Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

[Terrotory Cluster] -= {'05'},

Counter = {'1'},

[Trade Channel Name] = {'Traditional Trade'},

SegmentationMapped={'Gold','Silver', 'Gold+'},

[Territory Cluster] -= {'Autonom. Distr'},

Country = {'Russia'},

Month

Distinct [Business Partner Parent ID]))

, Outlet))

Re: Expression that assumes a selection is made

Anonymous — Thu, 06 Jul 2017 13:40:43 GMT

Hi Sunny

I've tried that but it did not work.

Kind regards

Nayan

Re: Expression that assumes a selection is made

Anonymous — Thu, 06 Jul 2017 13:42:48 GMT

Hi All

Managed to solve the issue. I've broken down each expression and what i have figured out is that if i take out the sum(aggr( , it works.

The expression give above was part of another expression.

Kind regards

Nayan

Re: Expression that assumes a selection is made

sunny_talwar — Thu, 06 Jul 2017 13:50:26 GMT

I think I missed the Month part....

Sum({<[Calendar Year/Month] ={'$(vYMLess1)'}, Month>} Aggr(

( Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

[Sales Org] = {'RU01'},

[Target Distribution Channel] = {'31','32'},

[Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

[Terrotory Cluster] -= {'05'},

Counter = {'1'},

[Trade Channel Name] = {'Traditional Trade'},

SegmentationMapped={'Gold','Silver', 'Gold+'},

[Territory Cluster] -= {'Autonom. Distr'},

Country = {'Russia'},

Month

Distinct [Business Partner Parent ID])

Count({<[Calendar Year/Month] ={'$(vYMLess1)'},

[Sales Org] -= {'RU01'},

[Business Partner (CRM): Transaction Blocking Reason] -= {25,26},

[Terrotory Cluster] -= {'05'},

Counter = {'1'},

[Trade Channel Name] = {'Traditional Trade'},

SegmentationMapped={'Gold','Silver', 'Gold+'},

[Territory Cluster] -= {'Autonom. Distr'},

Country = {'Russia'},

Month

Distinct [Business Partner Parent ID]))

, Outlet))

Re: Expression that assumes a selection is made

Anonymous — Thu, 06 Jul 2017 14:17:43 GMT

Hi Sunny

Thank you for your respond, I have added the month as you have suggested, but did not come right. If i do get a chance, will try to update a sample model.

Kind regards

Nayan

Re: Expression that assumes a selection is made

sunny_talwar — Thu, 06 Jul 2017 14:19:45 GMT

Sounds good... but if without Sum(Aggr()) is working for you... I would not recommend adding additional functions as Aggr() makes your app slower....