topic Re: 3Month loss... in QlikView

3Month loss...

kavita25 — Fri, 10 Mar 2017 06:44:05 GMT

Hi,

I want to show volume loss for dealers..i.e If the dealer is billed in May and no sale in next 3 months i.e Jun,Jul, Aug.

So for that dealer is loss for the month of May.

In simple words if May is >0 and Jun,Jul,Aug sale is <0 then it is loss for that month..

Similarly I have to show for Jun and next 3 months, Jul..and so on...

My dimension is Month field..

swuehl‌ - You helped me to get next 3 months bill..How to show repeat purchase.. in this post..

Kindly suggest me to solve this issue...where the next 3 months is in loss..

Regards,

Kavita

Re: 3Month loss...

vinieme12 — Fri, 10 Mar 2017 09:50:06 GMT

Like below

=AVG({<DistributorCode = {"=sum([Total Turn Over]) > 0 and sum({<InvMonth,InvMonth = {">$(vSelectedMonth)<=$(vEndMonth)"}>}[Total Turn Over])=0"}>}1)

Create a field to have numeric values of each month

where;

vSelectedMonth = Num of selected InvMonth

vEndMonth = $(vSelectedMonth)+3

Re: 3Month loss...

kavita25 — Fri, 10 Mar 2017 10:24:16 GMT

My dimension is month field..

It should show distinct count of outlet code if May has sale (white part) and if Jun,Jul,Aug has no sale (i.e in grey).

same it should calculate for all the months.

For e.g 9110012201324 this code has sale on Feb Month, but no sale in Mar,Apr and May month..

So the bar chart for this dealer code will be shown in Feb month only and not rest of the month coz rest of the month has data.

Regards,

Kavita

Re: 3Month loss...

vinieme12 — Fri, 10 Mar 2017 10:28:31 GMT

Use the same

=Count({<DistributorCode = {"=sum([Total Turn Over]) > 0 and sum({<InvMonth,InvMonth = {">$(vSelectedMonth)<=$(vEndMonth)"}>}[Total Turn Over])=0"}>}DistributorCode )

Re: 3Month loss...

kavita25 — Fri, 10 Mar 2017 10:37:34 GMT

Its showing some error in set modifier...

Re: 3Month loss...

kavita25 — Fri, 10 Mar 2017 10:38:36 GMT

Expression Used:

=Count(distinct {<[DMS Outlet Code] = {"=sum(Qty) > 0 and sum({<InvFiscal_Month,InvFiscal_Month = {">$(vSelectedMonth)<=$(vEndMonth)"}>}Qty)=0"}>}[DMS Outlet Code] )

vSelectedMonth =num(InvFiscal_Month)

vEndMonth =$(vSelectedMonth)+3

Re: 3Month loss...

vinieme12 — Fri, 10 Mar 2017 10:57:02 GMT

check the attached

Re: 3Month loss...

kavita25 — Fri, 10 Mar 2017 11:31:55 GMT

Its not working..as per the logic...

9110012201315 this has value for Aug Sep Oct Nov..so this should not come...

Coz its next 3 month if I start from May has no value till Jul and Aug to Nov has value...

My logic is it should have sale for the particular month and next 3 month should not have sale...

Re: 3Month loss...

vinieme12 — Fri, 10 Mar 2017 11:39:07 GMT

you need to make a selection in the Month field!!

Re: 3Month loss...

kavita25 — Fri, 10 Mar 2017 11:40:51 GMT

Yess...

Re: 3Month loss...

vinieme12 — Fri, 10 Mar 2017 12:07:53 GMT

You need to make a selection in the Month Field! otherwise the variables won't evaluate

Re: 3Month loss...

kavita25 — Fri, 10 Mar 2017 12:11:41 GMT

even if i do selection, its showing me wrong count...as some dealers should not come...but they are considered.

9110015100041,9110012201322 should not be considered...

Re: 3Month loss...

kavita25 — Fri, 10 Mar 2017 12:13:53 GMT

stalwar1‌ gwassenaar‌ swuehl‌

Please suggest me some idea..to solve this issue.

Re: 3Month loss...

vinieme12 — Fri, 10 Mar 2017 12:28:07 GMT

these don't come!! what are you selecting?

9110015100041,9110012201322

Re: 3Month loss...

kavita25 — Sat, 11 Mar 2017 06:07:00 GMT

I am selecting the same month and code...

It's showing me Aug in bar chart...

Re: 3Month loss...

kavita25 — Sat, 11 Mar 2017 06:40:33 GMT

Hi stalwar1‌

I want those Outlet code where its next 3 month has no sale...but the particular month has sale..

9110012202628 this code should come under Nov Month-- Coz it has sale in the month of Nov but not in Dec Jan Feb...

Hope so now ur clear with my requirement...

How to show repeat purchase.. In this post... I wanted particular month and next 3 month should have sale..

Now this requirement is opposite of it..

Can u Please suggest me some ideas...

Regards,

Kavita

Re: 3Month loss...

kavita25 — Tue, 14 Mar 2017 09:09:06 GMT

Hi,

Please help me with this issue or suggest me some ideas to resolve this.

stalwar1‌

vinieme12‌

swuehl‌

gwassenaar‌

Regards,

Kavita

Re: 3Month loss...

kavita25 — Wed, 15 Mar 2017 09:55:50 GMT

Can anyone please help me with this issue...

vinieme12‌

swuehl‌

stalwar1‌

Re: 3Month loss...

vinieme12 — Wed, 15 Mar 2017 10:15:59 GMT

Can you post post th app that you are using so I can Make corrections to the same

Re: 3Month loss...

kavita25 — Wed, 15 Mar 2017 10:32:12 GMT

Yes..

PFA...