unique prod id

[#1]

Hi,

I do have requirement where need to calculate whether prod has been upgraded or downgraded based on three grading systems, the issue is if I prod has been upgraded or downgraded into ratings, i need to count only once.

for example conisder below data

Company	Product Id	Business date	Grade 1	Grade 2	Grade 3
Microsoft	A123	6/29/2015	10	10	10
Microsoft	A123	6/28/2015	9	9	10
Microsoft	A456	6/29/2015	10	10	10
Microsoft	A456	6/28/2015	10	10	11
Microsoft	A789	6/29/2015	13	12	12
Microsoft	A789	6/29/2015	12	12	12
Duke	B123	6/28/2015	9	12	13
Duke	B123	6/29/2015	9	12	14
Edison	B456	6/28/2015	11	13	13
Edison	B789	6/29/2015	9	9	12
Edison	B789	6/28/2015	10	10	9

logic for upgrades: if difference of previous grade-current grade>0, then upgrade

logic for downgrades: if difference of previous grade-current grade<0, then downgrade

as you can see in above example prod A123 of microsoft has been upgraded in both grade 1 and grade 2 and in grade 3 its unchanged,

so, if the product has been upgraded in at least one of grades it should count as only 1 upgrade, so in total Microsoft has two upgraded products A123(in grade 1 and grade2) and A 789, hence count is 2 and for Duke product has been downgraded in grade 3.

o/p table should be

Company	Total Upgrades	Total Downgrades
Microsoft	2	1
Duke	0	1
Edison	1	1

Can you please let me know the logic for this.  Any help is highly appreciated.

Thanks,

[jagan]

Hi,

Try this script

Temp:

LOAD

Company, [Product Id], Date([Business date]) AS [Business date], [Grade 1], [Grade 2], [Grade 3]

INLINE

[   

    Company, Product Id, Business date, Grade 1, Grade 2, Grade 3

    Microsoft, A123, 6/29/2015, 10, 10, 10

    Microsoft, A123, 6/28/2015, 9, 9, 10

    Microsoft, A456, 6/29/2015, 10, 10, 10

    Microsoft, A456, 6/28/2015, 10, 10, 11

    Microsoft, A789, 6/29/2015, 13, 12, 12

    Microsoft, A789, 6/29/2015, 12, 12, 12

    Duke, B123, 6/28/2015, 9, 12, 13

    Duke, B123, 6/29/2015, 9, 12, 14

    Edison, B456, 6/28/2015, 11, 13, 13

    Edison, B789, 6/29/2015, 9, 9, 12

    Edison, B789, 6/28/2015, 10, 10, 9

];

Data:

LOAD

*,

If(Peek(Company) = Company AND Peek([Product Id]) = [Product Id], If([Grade 1] > Peek([Grade 1]), 1, 0) +  If([Grade 2] > Peek([Grade 2]), 1, 0) + If([Grade 3] > Peek([Grade 3]), 1, 0)) AS Upgrade,

If(Peek(Company) = Company AND Peek([Product Id]) = [Product Id], If([Grade 1] < Peek([Grade 1]), 1, 0) +  If([Grade 2] < Peek([Grade 2]), 1, 0) + If([Grade 3] < Peek([Grade 3]), 1, 0)) AS Downgrade

RESIDENT Temp

ORDER BY Company, [Product Id], [Business date];

DROP TABLE Temp;

Now in chart use

Sum(Upgrade) For getting Upgrade count

Sum(Downgrade) For getting Downgrade count

Hope this helps you.

Regards,

Jagan.

[qlikviewwizard]

Hi qkvw1234,

Please follow jagan‌ 's Advise. I used pivot table.

Enclosed the app for your reference.

[#4]

Hi Jagan,

can this be done in UI. ? also if solution if is at script level, i do have multiple dates how do I compare selected date and its previous date. please help..

[jagan]

Hi,

It is very difficult to do this in front end, you have to do lot of complex expressions.  The best thing is you can do this at script level.